timeGroup function group data on Thursday

Environment :

  • Grafana version: latest version 7.0.1
  • Data source type & version: mysql
  • OS Grafana is installed on: centOS
  • User OS & Browser:
  • Grafana plugins:
  • Others:

Hi,

i have a question regarding this timeGroup function, when i use

$__timeGroup(updatetime,‘7d’, 0) as time,

The data is grouped weekly but start from Thursday, can it be from Monday as most of users expect ?
Also from the Query Inspector, it seems the time is deviated by 7 hours
I would expect something like
2020-06-22 00:00:00
2020-06-29 00:00:00

can anyone look into this issue?

It is because of the way that the $__timeGroup() function calculates bins of 7 days. In the case of MS SQL Server, it applies the following:
FLOOR(DATEDIFF(second, ‘1970-01-01’, <time_column>)/604800)*604800

1970-01-01 is considered “0”. It also happened to be a Thursday. 604800 is the number of seconds in a week (60 x 60 x 24 x 7).

The DATEDIFF()/604800 portion calculates the number of elapsed weeks, including partial week, since 1970-01-01, with second precision.
The FLOOR part drops any fractional week from weeks elapsed, then calculates the number of seconds corresponding to the number of whole weeks elapsed, which returns a ‘7-day bin starting on a Thursday’.

$__timeGroup() really should allow a referential offset, but doesn’t.

Rather than using $__timeGroup() you may wish to affect you own FLOOR logic based upon 1970-01-01 + Xday offset to reset your “0” point to the following desired start day. For example: 1970-01-04 (Sunday) or 1970-01-05 (Monday).
You could use a dashboard variable to map multiple bin widths (hour (3600sec), day (86400sec), week (604800sec), month, etc.) to allow for quick precision changes.