Question 232987 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql query to round up timestamp to nearest 10th minute

I am trying to create a query which always round up the timestamp to the next nearest 10 minutes. The solutions available online are not working for me.

I have tried below solutions but not giving me 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 am looking for a query which will convert timestamp values as below. I tried to use date_trunc operation but not working

2023-07-01 10:00:00 -> Output should be '2023-07-01 10:10:00'
2023-07-01 10:00:01 -> Output should be '2023-07-01 10:10:00'
2023-07-01 10:01:00 -> Output should be '2023-07-01 10:10:00'
2023-07-01 10:05:00 -> Output should be '2023-07-01 10:10:00'
2023-07-01 10:09:59 -> Output should be '2023-07-01 10:10:00'
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search