Group by average intervals across a timeframe
So let's say that I want to keep track of my CPU temperature, with simple columns 'date' and 'temperature'. I'd like to see what period saw the highest temperatures on average in the last week. I capture the data every 10 minutes, so I want each 10 minute block averaged with the same block from the other days of the week.
So for example:
2018-01-08 02:00:00 78.3 2018-01-08 03:00:00 81.2 2018-01-09 02:00:00 74.1 2018-01-09 03:00:00 75.9
I would want the averages of each day @ 02:00:00, each day @ 03:00:00, and so on. (except the real data is every 10 minutes) The exact datetime varies - it's not always 02:00:02, sometimes it could be 02:00:07, etc., so I can't just do an exact conditional.
Any idea how I'd go about making this data? I assume there's some way I can use GROUP BY for this, but I'm lost as to how.
Format just the hour and minute, and group by that.
SELECT DATE_FORMAT(date, '%H:%i') AS time, AVG(temperature) AS temp FROM yourTable GROUP BY time
This assumes that the readings are never delayed by more than a minute from the expected 10-minute periods -- if the reading for
02:11:01it will not be put in the group.