How to group datetime into intervals of 3 hours in mysql

userRJib 0
Viewed 53

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)
aggregate-functions mysql-8.0 datetime mysql sql
2 Answers
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

Prasanna Kumar J0

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.

O. Jones0
Related