'How to break datetime in 12 hour chunks and use it for aggregation in Presto SQL?

I have been trying to break the datetime in 12 hour chunk in Presto SQL but was unsuccessful.

Raw data table:

datetime Login
2022-05-08 07:10:00.000 1234
2022-05-09 23:20:00.000 5678
2022-05-09 06:20:00.000 5674
2022-05-08 09:20:00.000 8971

The output table should look like below. I have to get count of login in 12 hour chunks. So, first should be from 00:00:00.000 to 11:59:00:000 and the next chunk from 12:00:00.000 to 23:59:00:000

Output:

datetime count
2022-05-08 00:00:00.000 2
2022-05-08 12:00:00.000 0
2022-05-09 00:00:00.000 1
2022-05-09 12:20:00.000 1
sqlpresto


Solution 1:[1]

This should work:

Extract the hour from the timestamp, then integer divide it by 12. That will make it 0 till 11:59, and 1 till 23:59. Then, multiply that back by 12.

Use that resulting integer to DATE_ADD() it with unit 'HOUR' to the timestamp of the row truncated to the day.

SELECT                                                                                                                                                                                                                                    
  DATE_ADD('HOUR',(HOUR(ts) / 12) * 12, TRUNC(ts,'DAY')) AS halfday
, SUM(login) AS count_login
FROM indata
GROUP BY
  halfday
;
-- out        halfday       | count_login 
-- out ---------------------+-------------
-- out  2022-05-08 00:00:00 |       15879
-- out  2022-05-08 12:00:00 |        5678

Solution 2:[2]

This query worked for me.

SELECT                                                                                                                                                                                                                                    
  DATE_ADD('HOUR',(HOUR(ts) / 12) * 12, date_trunc('DAY',ts)) AS halfday
, SUM(login) AS count_login
FROM indata
GROUP BY
  halfday
;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 marcothesane
Solution 2 Bishu

Related Questions