$__timeGroup +2hrs in aggregation per day

Grafana MSSQL
, ,
1

What do you see:
SELECT
$__timeGroup(timestamp, ‘1d’) AS time,
sum(value) as value,
Measures as metric
FROM [Data]
where $__timeFilter(timestamp)
group by $__timeGroup(timestamp, ‘1d’), Measures
ORDER BY 1 ASC
I have build a SQL query that will Aggregate my my data per day as depicted.

image
image984×401 8.84 KB

My date is coming from an MSSql database with my timestamp being in UTC format.
The issue:
Aggregating the time per day will lead to a +2hrs offset in the aggregation meaning that a “day” is starting at 02:00 instead of 00:00. That means that my value is wrong for my purpose because I want to have the sum of my local day time not UTC. so what should happen is this:

the sum of April 16th (so starting at 16th April 00:00 ending at 16th April 23:59) is equal to 700. on April 17 there the sum is 0 because my device is completely off.
because of the time zone the sum of April 16 is 630 and April 17 is 70, because it will aggregate to April 17 02:00.

I have searched and grinded the forums but couldn’t find an answer that will help me. In fact there are 2 other threads with almost exactly the same issue but unanswered completely.

I don’t want to change the global dashboard timeboard setting, because this doesn’t in fact is changing anything in the calculation. Besides that, I don’t want this to be globally active in all my visualizations.

I hope someone can help me.
Best wishes

2

Before you group the time by day in your SQL query, first convert the timestamp from UTC to your local time (UTC+2).This way, the day will start at midnight (00:00) in your time zone — not at 2 AM.
DATEADD(HOUR, 2, timestamp)
try this:
SELECT
$__timeGroup(DATEADD(HOUR, 2, timestamp), ‘1d’) AS time,
sum(value) as value,
Measures as metric
FROM [Data]
WHERE $__timeFilter(timestamp)
GROUP BY $__timeGroup(DATEADD(HOUR, 2, timestamp), ‘1d’), Measures
ORDER BY 1 ASC

Points

  • DATEADD(HOUR, 2, timestamp) adds 2 hours to the original UTC time — this converts it to your local time.
  • __timeGroup(..., '1d') then groups the data by full local day (from 00:00 to 23:59).
  • __timeFilter(timestamp) still works as usual — filtering your data by the dashboard time range.
  • This change only affects this panel, not your whole Grafana dashboard.

If your time zone is different than UTC+2, replace the 2 with the correct number of hours.

1 Like
3

thank you for your response. Unfortunately I get an error:
Interpolation failed: error parsing interval 2

I’ve searched for that error and it appears that $__timegroup is not able to handle multiple nested arguments.
I’ve seen people recommend using a nested From clause to do the dateadd method in there but I don’t know how to do that.

4 
SELECT
  $__timeGroup(shifted.local_time, '1d') AS time,
  SUM(shifted.value)      AS value,
  shifted.Measures        AS metric
FROM (
  SELECT
    DATEADD(HOUR, 2, timestamp) AS local_time,
    value,
    Measures
  FROM [Data]
  WHERE $__timeFilter(timestamp)
) AS shifted
GROUP BY
  $__timeGroup(shifted.local_time, '1d'),
  shifted.Measures
ORDER BY 1 ASC;

note : Just replace the 2 in DATEADD(HOUR, 2, …) with whatever offset you need (e.g. 5 for UTC+5, or 5.5 if you convert to minutes: DATEADD(MINUTE, 330, timestamp) for UTC+5:30).

5

What time zone are you in? also what do you mean the datetime is UTC format?

6

thank you. that is what I was looking for!

1 Like
7

That looks like a solution, which must be adjusted twice yearly when daylight saving time changes.

2 Likes
8

Thank you for your kind words! I truly appreciate your message—it means a lot. :blush:

9

You’re most welcome! I’m really glad it was exactly what you needed.

10

As @jangaraj pointed out this is not a proper solution

It does not address the underlying issue which most probably is your data not being UTC or your browser settings as far as time zone etc not being set correctly

11

they are. My underlying data is in UTC. that’s what’s causing my issue in the first place. while I do agree that I have to manually adjust the solution 2 times a year it is really not that bad for my purpose. Also, I think there is a way to adjust the query further, so it will adjust to that accordingly

1 Like
12

yes that is good for you but since this is a public forum probably not the best solution to share to the world.

Can you share DDL and DML with real world sample data so we can test this out locally and look to see if we can come up with a more robust solution?

select getdate() , GETUTCDATE(), SYSDATETIMEOFFSET()

if you ran the above you get local_datetime, pure utc datetime, local date time offset which shows you how much it is offset from UTC