How to group time data into intervals day and night?


I’m using InfluxDB and Grafana to visualize some data recorded in Home Assistant, particularly temperatures.

I would like to group data into average day and night temperatures (say, average all data from 0700-1900 and 1900-0700).

Can any kind user give a layman a hint, how to do that? Googling was of no help, only found out, how to group by time slots of the same day by fiddling around with GROUP BY in SELECT mean("value") FROM "°C" WHERE ("entity_id" = 'thermostat_hc1_current_room_temperature') AND $timeFilter GROUP BY time($__interval) fill(previous)

Thanks, Stefan

Welcome and good first question!

Maybe this?

Query: SELECT moving_average(mean(“Actual_Value”), 5) FROM “stations” WHERE $timeFilter GROUP BY time(12h) fill(null)

gives me a yellow line that plots a single point every 12 hours (07:00 and 19:00, for the prior 12 hours). Each single point appears to be the average of the prior 12 hours’ readings.

I thought about grouping by 12h, but doesn’t that group 00:00 to 12:00 and 12:00 to 24:00? How did you move it to 07:00 to 19:00 and 19:00 to 07:00? In other words, where in your statement do you specify the start of the interval? I’ll probably overlook something here :wink:

You probably overlook advanced group by time syntax - there is offset_interval, so you can customize it.

Thanks for the hints. Do I assume that each point shows the mean of the next 12 hours?

SELECT mean("value") FROM "°C" WHERE ("entity_id" = 'thermostat_damped_outdoor_temperature') AND $timeFilter GROUP BY time(12h,6h) fill(null)