Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Round date to 10 minutes interval

I have a DATE column that I want to round to the next-lower 10 minute interval in a query (see example below).

I managed to do it by truncating the seconds and then subtracting the last digit of minutes.

WITH test_data AS (
        SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:09:59', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2010-01-01 10:10:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
  UNION SELECT TO_DATE('2099-01-01 10:00:33', 'YYYY-MM-DD HH24:MI:SS') d FROM dual
)
-- #end of test-data
SELECT
  d, TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60)
FROM test_data

And here is the result:

01.01.2010 10:00:00    01.01.2010 10:00:00
01.01.2010 10:05:00    01.01.2010 10:00:00
01.01.2010 10:09:59    01.01.2010 10:00:00
01.01.2010 10:10:00    01.01.2010 10:10:00
01.01.2099 10:00:33    01.01.2099 10:00:00

Works as expected, but is there a better way?

EDIT:

I was curious about performance, so I did the following test with 500.000 rows and (not really) random dates. I am going to add the results as comments to the provided solutions.

DECLARE
  t       TIMESTAMP := SYSTIMESTAMP;
BEGIN
  FOR i IN (
    WITH test_data AS (
      SELECT SYSDATE + ROWNUM / 5000 d FROM dual
      CONNECT BY ROWNUM <= 500000
    )
    SELECT TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60)
    FROM test_data
  )
  LOOP
    NULL;
  END LOOP;
  dbms_output.put_line( SYSTIMESTAMP - t );
END;

This approach took 03.24 s.

like image 530
Peter Lang Avatar asked Feb 03 '10 14:02

Peter Lang


People also ask

How to round minutes in oracle?

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY HH24:MI:SS'; SELECT SYSDATE , TRUNC (SYSDATE) + ( ROUND ( (SYSDATE - TRUNC (SYSDATE)) * 48 ) / 48 ) FROM dual; In this case, there are 48 periods in a day. Multiplying by 48 converts days to periods, dividing by 48 converts periods to days.

How do you round off minutes in SQL?

This will round up or down to the nearest 15 minutes. SELECT DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, GETDATE()) / 15.0, 0) * 15, 0);

Can you round dates SQL?

ROUND returns date rounded to the unit specified by the format model fmt . The value returned is always of datatype DATE , even if you specify a different datetime datatype for date . If you omit fmt , then date is rounded to the nearest day.


2 Answers

select
  trunc(sysdate, 'mi')
  - numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10), 'minute')
from dual;

or even

select
  trunc(sysdate, 'mi')
  - mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)
from dual;
like image 153
Tom Avatar answered Oct 26 '22 08:10

Tom


I generally hate doing date -> character -> date conversions when it's not necessary. I'd rather use numbers.

select trunc((sysdate - trunc(sysdate))*60*24,-1)/(60*24)+trunc(sysdate) from dual;     

This extracts the minutes from the current day, truncates them down to the 10-minute interval, and then adds them back in to make it a date again. Of course, you can replace sysdate with whatever date you want. It trusts implicit conversions a lot more than I want but at least it'll work for any NLS date format.

like image 26
Jim Hudson Avatar answered Oct 26 '22 09:10

Jim Hudson