How to make the time of a timestamp the top of the hour?

Question

I am trying to convert a timestamp that contains minutes and seconds to just be a timestamp at the hour interval

i.e. 2022-05-03 11:14:59.000 becomes 2022-05-03 11:00:00.000

I have tried a number of different things and I get an error or it doesn't give the result I want. I am sure there is a simple solution for this, but I have been attempting it for so long, my brain cannot see a simple solution anymore.

I tried doing to_char((extract(hour from data_timestamp)||':00'), 'HH24:MI') as date_timestamp but the : would cause an error.

I also tried to just get the time by doing to_char(extract(hour from data_timestamp), 'HH24:MI') but the result was HH24:

Answer 1

You can use DATE_TRUNC function - Amazon Redshift:

DATE_TRUNC('datepart', timestamp)

datepart: The date part to which to truncate the timestamp value. The input timestamp is truncated to the precision of the input datepart. For example, month truncates to the first day of the month.

They also provide an example:

SELECT DATE_TRUNC('hour', TIMESTAMP '20200430 04:05:06.789');

date_trunc
2020-04-30 04:00:00