I’m trying to plot a time series graph which starts at 0 on each new day. I have an increasing number ID and a timestamp TimestampStarted for each row which represents a task, and I’m able to plot a cumulative graph displaying how tasks increase over time. However, I would want to have the count start at 0 each day, and cumulatively increase as tasks get started, and would start from 0 again the next day.
In the following picture the green line is what I have, and the red line represents what I want to have.
My query is currently simply:
SELECT
ID as Count,
TimestampStarted as time
FROM
tbl_JobLog3
ORDER BY 1
I have tried finding a solution without success, so I’m wondering if what I’m trying to achieve is even possible in Grafana, or is it maybe possible using some query magic?
The answer was simply using multiple queries. My queries looked like this:
SELECT
DATEADD(HOUR, -3, TimestampStarted) as time, /I had to remove 3 hours as the database as my time (UTC+3) was added twice/
(row_number() OVER (ORDER BY(SELECT(0))))/2 as ‘2 days ago’ /We number each row in the query starting from 0 and divide by two as one mission is composed of 2 TimestampStarted/
FROM
tbl_JobLog3
WHERE /We choose all jobs that happened 2 days ago/
TimestampStarted > CURRENT_TIMESTAMP - DAY(1) - CONVERT(char(8), CURRENT_TIMESTAMP, 108) AND TimestampStarted < CURRENT_TIMESTAMP - DAY(0) - CONVERT(char(8), CURRENT_TIMESTAMP, 108)
ORDER BY
TimestampStarted
In the WHERE part modifications are needed in the DAY(X) part to separate queries to different dates, like “2 days ago” or “5 days ago” for each graph individually.
Then I used the option stacked line to get them connected.
