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

Answer 2

Since Postgres 14, there is date_bin() to truncate to the nearest time interval. (Similar to date_trunc(), which can only truncate to full units (1 minute, 1 hour, etc.).

date_bin('10 min', ts, '2000-1-1')

But you want the ceiling, not the floor.
Your expected output indicates you are satisfied with adding 10 minutes:

date_bin('10 min', ts, '2000-1-1') + interval '10 min'

Also ~ 5x faster than manual computations.
To truly round up (keep exact matches instead of incrementing by 10 minutes):

CASE WHEN date_bin('10 min', ts, '2000-1-1') = ts
     THEN ts
     ELSE date_bin('10 min', ts, '2000-1-1') + interval '10 min' END

Or shorter and a bit faster, yet (if not as clear):

COALESCE(NULLIF(date_bin('10 min', ts, '2000-1-1'), ts) + interval '10 min', ts)

fiddle

See:

• What is the fastest way to truncate timestamps to 5 minutes in Postgres?

Answer 3

Great question! Postgres does not provide a built-in function to get this exact behavior.

You could, however, accomplish this using date manipulation functions. A query to do that would be:

SELECT    
     timestamp_value,
    (date_trunc('hour', timestamp_value) +
    CEIL(EXTRACT(MINUTE FROM timestamp_value) / 10) * INTERVAL '10 minutes') AS rounded_timestamp_value
FROM timestamps_table;

Of course, this query makes a few assumptions:

1. You have a table named timestamps_table
2. A field timestamp_value stores the timestamps.

Test it here