Issues with select last() when using group by time(1d)

Grafana Time Series Panel
1

When using SELECT last() to get the last value of my average value it works fine when using GROUP BY time(1) but when using GROUP BY time(1d) it uses a value from the next day.

Needed value is 3.40 but when using time(1d) the value is 4.67

Anyone here know why this is happening and how to fix it?

last(1d)
last(1d)800×600 77 KB

2

That looks like a timezone issue. Keep in mind:

  • Grafana “moves” query result to your browser timezone (it looks like you are in UTC+1), you can configure this behaviour with dashboard timezone config
  • InfluxDB groups by default in UTC timezone, of course it provides some option to use different timezone

Make sure you have everything in UTC timezone first: data, dashboard timezone, influxdb groupping. And then do a fine zone tunning until you will have desired result.

3

Thx for your reply @jangaraj but can’t figure it out. In home assistant db, influx db and grafana all have 3.40 as the last value on 2021-01-10.Every choice of grouping shows the right last value accept for time(1d).

The first value after 3.4 (2021-01-10 23:59:36) is 4.7 (2021-01-11 00:00:06) the day after. So why use that value when using time(1d) ?
last_value

Only strange thing i see is in influxdb on 2021-01-11 00:00:00 there is a 4.67 value that isn’t shown in grafana or home assistant DB.

last_value (2)

4

What is the timeframe you select from?

Might be similar to my issue? Graph "per day" in a time-dynamic panel