I have a number of charts that display daily values I am recording (weather, power usage, etc). I have the graphs configured to show the previous 7 days of data and I believed until just now that these displayed from midnight to midnight. I just realized, they are actually grouped by 24 hours broken by the current time. Is there a way to change this without creating major headaches so it does current date, day-1, day-2, etc rather than last 24h, previous 24h, etc?
As you can see from the image, the temperature on 6/29 from top bar chart shows peak temp as 109F but the lower time line chart shows 109F occurred at 17:00 on 6/28. The peak temperature between 00:00 and 23:59 on 6/29 was 103F at ~15:30.
The answer is probably “yes you can do that”, but the way you do it will depend entirely on your datasource. Can you share more about that, as well as the query you’re currently running?
Finally, what time zone are you in? Most database engines will use UTC time boundaries for daily aggregation unless instructed otherwise.
The data is just from my weather station so nothing secret there. I set the local time to browser time (which is PST, correct) so assumed that would automatically deal with time localization. If I need to adjust another setting, I can try that too.
SELECT max("value") FROM "rtl_433/Acurite-5n1/A/temperature_F" WHERE $timeFilter GROUP BY time(1d)
I’m assuming the issue is that my GROUP BY time is 1 day rather than 00:00 to 23:59. I’m not sure how to format that correctly, so any suggestions would be great.
And just for clarity, I’m using InfluxDB 1.8 as the data source and Grafana v7.3.6 to display.
Ok, so you’re using Influx. In that case you need to add something like tz('America/Los_Angeles') at the end of your query, in order to use the correct date boundaries (and not UTC). The rest of what you have looks right. See Explore data using InfluxQL | InfluxDB OSS 1.8 Documentation for more detail.
I think that has worked. The max temperature for 6/28 and 6/29 show as 118 and 103, respectively. The min temp also makes sense so presumably the average value is calculated correctly as well.
It doesn’t seem to show up when I go back to the standard view so I wasn’t sure if there was a clean way of adding it so it doesn’t appear hidden.
Yes, that looks right. The option does exist in the regular query editor, but I think only for newer versions of Grafana (see InfluxDB data source | Grafana Labs)
