How to group datetime into intervals of 3 hours in mysql-CodePudding

I have the follwoing table structure

start	count
2022-08-02 22:13:35	20
2022-08-03 04:27:20	10
2022-08-03 09:21:48	10
2022-08-03 14:25:48	10
2022-08-03 14:35:07	10
2022-08-03 15:16:09	10
2022-08-04 07:09:07	20
2022-08-04 10:35:45	10
2022-08-04 14:42:49	10

I want to group the start column into 3 hour intervals and sum the count

like follows

interval	count
01h-03h	400
03h-06h	78
...	...
...	....
20h-23h	100
23h-01h	64

I have the following query but am not sure how to proceed from here

select hour(start), sum(count) from `table`
GROUP BY hour(start)

CodePudding user response：

select hr_range,sum(res.cnt)
    from (
        select hour(start) hr,
        case when hour(start) between 0 and 3 then '00h-03h'
            when hour(start) between 4 and 6 then '04h-06h'
            when hour(start) between 7 and 9 then '07h-09h'
            ....
            when hour(start) between 21 and 23 then '21h-23h'
            end as hr_range,
        sum(count) as cnt from `table`
        GROUP BY hour(start)
    )res
group by hr_range

I think this is one of the way to solve the issue

CodePudding user response：

To do this you need to be able to take any DATETIME value and truncate it to the most recent three-hour boundary. For example you need to take 2022-09-06 19:35:20 and convert it to 2022-09-06 18:00:00.

Do that with an expression like this:

DATE(start)   INTERVAL (HOUR(start) - MOD (HOUR(start), 3)) HOUR

This truncates the value to the nearest DATE(), then adds back the correct number of hours.

So a query might look like this:

SELECT DATE(start)   INTERVAL (HOUR(start) - MOD (HOUR(start), 3)) HOUR,
       SUM(count)
  FROM table
 GROUP BY DATE(start)   INTERVAL (HOUR(start) - MOD (HOUR(start), 3)) HOUR

The trick to solving this problem of aggregating database rows over blocks of time is, generally, to come up with the appropriate way of truncating the DATETIME or TIMESTAMP values. Writeup here.

And if you want to aggregate by 3 hour intervals, with all days gathered into a single result of eight rows, do this.

SELECT HOUR(start) - MOD(HOUR(start, 3)),
       SUM(count)
  FROM table 
 GROUP BY HOUR(start) - MOD(HOUR(start, 3))

Again, you use an expression to truncate each value to the appropriate block of time.