我有一个DATE列,我想在查询中舍入到下一个低10分钟的间隔(见下面的示例).
我设法通过截断秒数,然后减去分钟的最后一位数来做到这一点.
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
结果如下：
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
按预期工作,但有更好的方法吗？
编辑：
我对表演好奇,所以我做了500.000行以下(并不是真的)随机日期的以下测试.我将把结果作为意见添加到提供的解决方案中.
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,10) / (24 * 60)
FROM test_data
)
LOOP
NULL;
END LOOP;
dbms_output.put_line( SYSTIMESTAMP - t );
END;
这种方法花了03.24秒.
解决方法
select trunc(sysdate,'mi') - numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)),10),'minute') from dual;
甚至
select trunc(sysdate,'mi') - mod(EXTRACT(minute FROM cast(sysdate as timestamp)),10) / (24 * 60) from dual;
