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.

1 answer

  • answered 2018-01-11 21:34 Barmar

    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:10 happens at 02:11:01 it will not be put in the group.