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
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.
@jerimiahmack If I understand your solution correcty, it isn’t possible to use an interval variable.
If I should use a custom variable and not an interval variable, I would as well not use the FLOOR method.
I achieve the desired output with this custom variable: 1d : DAY(s.start_at),1w : WEEK(s.start_at\, 1),1M : MONTH(s.start_at)
