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.
