Graph "per day" in a time-dynamic panel

Grafana Time Series Panel
,
1

Hi,

I’m trying to create a graph based on amount of stuff created per day.

At the time my query looks like this (anonymized):

SELECT  $__timeGroup(dateCreated, '24h', 0) as time, count(*) as Created
  FROM %database%
JOIN %stuff%
WHERE
  $__timeFilter(dateCreated) and %stuff%
GROUP BY
  $__timeGroup(dateCreated,'24h', 0)
ORDER BY
  max(dateCreated) ASC

The issue arises when i use the timepicker to select like “last 30 days”, as the start time will then be whatever time it is right now, from whatever time it is right now, 30 days ago.

image

This means that the last 24h, is not from today, but a combination of today and yesterday.

Can anyone help me create a better SQL?

1 Like
2

Hi,

It is resolved now :slight_smile:

solution:

SELECT  dateadd(DAY,0, datediff(day,0, dateCreated)) as time, count(*) as 'Created'
  FROM %database%
JOIN %stuff%
WHERE
  $__timeFilter(dateCreated) and %stuff%
GROUP BY
  dateadd(DAY,0, datediff(day,0, dateCreated))
ORDER BY
  max(dateCreated) ASC
3 Likes