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.
