Round date to 10 minutes interval

Question

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.

Answer 1

You could take the returned value as a string and substring the left side up to the last minute digit and replace it with a 0. I wouldn't exactly say thats better unless you provide some kind of metric.

Answer 2

Not necessarily any better, but another method:

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) + FLOOR((d-TRUNC(d))*24*6)/(24*6)
FROM test_data

Answer 3

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.

Answer 4

Another method,

select my_date - mod( (my_date-trunc(my_date))*24*60, 10)/24/60
from (
  select sysdate my_date from dual
);

An alternative that might be quicker as it removes the call to trunc.

select my_date - mod( (my_date-to_date('1970', 'yyyy'))*24*60, 10)/24/60
from (
  select sysdate my_date from dual
);

Answer 5

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;