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.

What about `SELECT CASE WHEN TO_CHAR(date_col, 'MI') BETWEEN 0 AND 10 THEN TO_DATE(TO_CHAR(date_col, 'YYYY') ||'-'|| TO_CHAR(date_col, 'MM') ||'-'|| TO_CHAR(date_col, 'DD') ||' '|| TO_CHAR(date_col, 'HH') ||':00', 'YYYY-MM-DD HH:MI') END` ? — OMG Ponies, Feb 03 '10 at 18:29
@OMG Ponies: Sorry, but that returns the full hour when `0<=MI<=10`, otherwise `NULL`. — Peter Lang, Feb 04 '10 at 07:04
@OMG Ponies: Ah, now I see your point, sorry. I tried an optimized version (`WHEN TO_CHAR(d, 'MI') < 10 THEN TO_DATE(TO_CHAR(d, 'YYYY-MM-DD-HH24') || ':00', 'YYYY-MM-DD HH24:MI' WHEN TO_CHAR(d, 'MI') < 20 THEN `...) but performance was worse than with the other solutions (`5.48 s`) and one would actually have to "clutter with the rest of the WHEN" ;-) Thanks anyway! — Peter Lang, Feb 04 '10 at 16:19

score 18 · Accepted Answer · edited Apr 16 '10 at 11:09

18

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;

edited Apr 16 '10 at 11:09

Peter Lang

54,264
27
148
161

answered Apr 15 '10 at 19:09

Tom

196
1
4

@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 Apr 16 '10 at 11:16

score 4 · Answer 2 · answered Feb 03 '10 at 15:15

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.

See performance test in edited question. This approach took `04.39 s`. — Peter Lang, Feb 03 '10 at 16:50

score 1 · Answer 3 · answered Feb 03 '10 at 14:10

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.

answered Feb 03 '10 at 14:10

Nick Larsen

18,631
6
67
96

1

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. – Craig Feb 03 '10 at 14:23
See performance test in edited question. This approach took `04.32 s`. – Peter Lang Feb 03 '10 at 16:49

score 1 · Answer 4 · answered Feb 03 '10 at 14:54

1

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

answered Feb 03 '10 at 14:54

Tony Andrews

129,880
21
220
259

See performance test in edited question. This approach took `04.16 s`. – Peter Lang Feb 03 '10 at 16:49
Accepted because it is short and quite readable. Thanks! Will stick with my solution though, as it takes less time. – Peter Lang Feb 10 '10 at 10:12
@Tony Andrews: Accepted Tom's answer instead of yours, since I find it more intuitive and performance is better. Hope you don't mind... – Peter Lang Apr 16 '10 at 11:18

a'r · Answer 5 · 2010-03-24T12:28:48.260

1

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

edited Mar 24 '10 at 12:28

answered Mar 17 '10 at 10:04

a'r

35,921
7
66
67

+1: Another interesting approach that works. Took `04.60 s` in my test, i guess the `mod` makes it slower than my approach. – Peter Lang Mar 17 '10 at 11:30
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. – a'r Mar 24 '10 at 12:29
@ar: Sorry, did not get notified about your update. Your updated query performs better indeed: `04.22 s` – Peter Lang Apr 16 '10 at 11:04

score 0 · Answer 6 · answered Mar 20 '15 at 15:44

To return the next upper 10 minute interval, I used the following query. I hope it'll be useful because I couldn't simply do a

trunc(sysdate, 'mi') + mod(EXTRACT(minute FROM cast(sysdate as timestamp)), 10) / (24 * 60)

I did this and it worked for me.

select 
case when mod(EXTRACT(minute FROM cast(sysdate as timestamp)),5) between 1 and 4
      then trunc(sysdate,'mi')+((5*TRUNC(EXTRACT(minute FROM cast(sysdate as timestamp))/5,
0)+5)-EXTRACT(minute FROM cast(sysdate as timestamp)))/1440
      else trunc(sysdate,'mi')
end
from dual

This is based on this post.

score 0 · Answer 7 · answered Oct 19 '15 at 08:23

I think to solve this there's a much easier and faster way to round to a next lower 10 seconds, 1 Minute, 10 Minute etc. interval. Try to manipulate your timestamp as a string using SUBSTR() like this:

SELECT 
SUBSTR(datetime(d),1,18)||'0' AS Slot10sec, 
SUBSTR(datetime(d),1,17)||'00' AS Slot1min, 
SUBSTR(datetime(d),1,15)||'0:00' AS Slot10min, 
SUBSTR(datetime(d),1,14)||'00:00' AS Slot1h, 
MY_VALUE
FROM MY_TABLE;

Round date to 10 minutes interval

7 Answers7

Linked