$__timeGroup(`CreatedDate`, '60m', 0) not filling missing point as 0

I am using Grafana 10.4.3 with MySQL, and below is the query I am using,

SELECT
  $__timeGroup(`CreatedDate`, '60m', 0) as `time`,
  SUM(`Value`) as `value`
FROM
  Order
WHERE
  $__timeFilter(`CreatedDate`)
GROUP BY
  time
ORDER BY
  time

As stated from the doc, using $__timeGroup(CreatedDate, ‘60m’, 0) should fill missing point as 0. However, the chart generated still didn’t have the missing point but only point that have data. Is this a bug or my problem? Thanks.

Provide that doc link and info about used DB, please

I have updated the question. Thanks.

  1. are you using time series format?
  2. what does query inspector show ?

1, yes
2, The data shown in the table has missing point same as the result query returned. AFAIK, the missing point is “patched” by Grafana after query returned but seems it didn’t. The query shown in query inspector as below,
SELECT
UNIX_TIMESTAMP(CreatedDate) DIV 86400 * 86400 as time,
SUM(Price) as value
FROM
Order
WHERE
CreatedDate BETWEEN FROM_UNIXTIME(1713371829) AND FROM_UNIXTIME(1715963829)
GROUP BY
time
ORDER BY
time

Thanks.

by time series format I mean this section of the UI

image

so using the query generated

SELECT
UNIX_TIMESTAMP(CreatedDate) DIV 86400 * 86400 as time,
SUM(Price) as value
FROM
Order
WHERE
CreatedDate BETWEEN FROM_UNIXTIME(1713371829) AND FROM_UNIXTIME(1715963829)
GROUP BY
time
ORDER BY

and you ran that in mysql query tool, does it work in filling the gap?

1 Like

Oh! You are right that I chose table in this sectino of the UI. After changing that to time series, the gap was filled! Thanks for your help.

1 Like