Query to round up timestamp to nearest 10 minutes

Question

I am trying to round up a timestamp to the next full 10 minutes.
I have tried below solutions but wasn't able to get my expected results

• Postgres: how do you round a timestamp up or down to the nearest minute?

• Postgresql round timestamp to nearest 30 seconds

• How to round to nearest X minutes with PL/pgSQL?

I also tried date_trunc(), but couldn't make it work.

Expected results:

input	expected output
2023-07-01 10:00:00	2023-07-01 10:10:00
2023-07-01 10:00:01	2023-07-01 10:10:00
2023-07-01 10:01:00	2023-07-01 10:10:00
2023-07-01 10:05:00	2023-07-01 10:10:00
2023-07-01 10:09:59	2023-07-01 10:10:00

Answer 1

Here is a way to do it :

select dtime,
       (date_trunc('hour', dtime) +
        (cast(extract(minute from dtime) as int)/10 + 1) * 10 * interval '1 minute' 
       ) as rounded_dtime
from mytable

Demo here