Round datetime column to nearest hour in AWS redshift

Question

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

Answer 1

The code is:

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