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.

Solution 1

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;

Solution 2

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.

Solution 3

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

Solution 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
);

Solution 5

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.

View more solutions

Comments

[Craig]

SELECT SYSDATE, TO_DATE(SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD HH24MI'),1, 12)||'0','YYYYMMDD HH24MI') FROM DUAL What he said in SQL Code and Also what he said about performance, I'm not sure which would be better, but yours would look simpler.

[Peter Lang]

See performance test in edited question. This approach took 04.32 s.

[Peter Lang]

See performance test in edited question. This approach took 04.16 s.

[Peter Lang]

See performance test in edited question. This approach took 04.39 s.

[Peter Lang]

Accepted because it is short and quite readable. Thanks! Will stick with my solution though, as it takes less time.

[Peter Lang]

+1: Another interesting approach that works. Took 04.60 s in my test, i guess the mod makes it slower than my approach.

[a'r]

I've posted an alternative, which removes a call to trunc. It will be interesting to see if there is any difference in your test.

[Peter Lang]

@ar: Sorry, did not get notified about your update. Your updated query performs better indeed: 04.22 s

[Peter Lang]

@Tom: Welcome to StackOverflow! Both approaches seem to work, first one took 04.18 s, second one only 03.33 s! Performance of your second query is almost the same as with my original query, but avoids the TO_CHAR, so I'm going to accept your answer. Thanks!

[Peter Lang]

@Tony Andrews: Accepted Tom's answer instead of yours, since I find it more intuitive and performance is better. Hope you don't mind...