CODEWITHSUNDEEP
sqldatetimeamazon-redshift
qazwsx123
qazwsx123

Reputation: 237

Round datetime column to nearest hour in AWS redshift

I have a table called orders which contains a column full of date timestamps

order_date
2021-04-15 15:48:32
2021-04-14 13:02:54
...etc

and I want to round them to the nearest hour in a new column, I can't find a function which does this. DATE_TRUNC('hour', datetime) always rounds down rather than to the nearest hour. Ideally the outcome would look like:

order_date             rounded_date
2021-04-15 15:48:32    2021-04-21 16:00:00
2021-04-14 13:02:54    2021-04-14 13:00:00

Upvotes: 4

Views: 4131

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

The code is:

date_trunc('hour', order_date + interval '30 minute')

Upvotes: 6

Related Questions