$__timeGroup interval of 1d when data is in UTC while want to group data in local time zone

Grafana Dashboards
1

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

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

All data is stored UTC in database. The data shown now is grouped as a day in UTC and so when viewed from local time zone +8, it is shifted for 8 hours. I expect that should be grouped in local timezone. Any suggestion how to deal with this? Thanks.

image
image1784×714 51.9 KB

2

Is it a good idea? How it should work during Daylight Saving Time switch? How it should work if user will switch dashboard/panel time zone?

I would say that processing in local timezone will make more complex issues.

3

I understand what you are concerning about, what I thought is the graph will change as the viewer time zone change, when grouping with 1d, 00:00 is always in local time. As for user to analyze in a local market, grouping at UTC 0 is not user friendly. Google Analytics would also allow user to choose their own timezone although I don’t know how they store their data.

If $__timeGroup is not supposed to deal with that, any work around I can do to achieve this? Thanks.

4