I have column date column, timestamp data type.
date
'2020-08-05 01:01:24.000'
'2020-08-05 02:02:24.000'
'2020-08-05 02:03:24.000'
I want to groupby day-hours.
So In MySQL I would do this by
select date_format( date, '%Y-%m-%d %h') as `day-hour`
from table
group by date_format( date, '%Y-%m-%d %h');
which would output:
day-hour
2020-08-05 01
2020-08-05 02
How can I do this in Presto?
You can cast those strings to TIMESTAMP and use date_trunc to truncate the timestamps to hours:
WITH data(x) AS (VALUES
'2020-08-05 01:01:24.000',
'2020-08-05 02:02:24.000',
'2020-08-05 02:03:24.000')
SELECT date_trunc('hour', CAST(x AS TIMESTAMP))
FROM data
GROUP BY 1
_col0
-------------------------
2020-08-05 01:00:00.000
2020-08-05 02:00:00.000
(2 rows)