周期到10分钟间隔 [英] Round date to 10 minutes interval
问题描述
我有一个 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双
UNION SELECT TO_DATE('2099-01-01 10:00:33','YYYY-MM-DD HH24:MI:SS')d FROM dual
)
- #测试数据结束
SELECT
d,TRUNC(d,'MI') - MOD(TO_CHAR(d,'MI'),10)/(24 * 60)
FROM test_data
这里是结果:
0 1.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
按预期工作,但有更好的方法吗? / p>
编辑:
我对表演好奇,所以我做了以下测试有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,'MI') - MOD(TO_CHAR(d,'MI'),10)/(24 * 60)
FROM test_data
)
LOOP
NULL;
END LOOP;
dbms_output.put_line(SYSTIMESTAMP - t);
END;
此方法采取 03.24 s
。 p>
select
trunc(sysdate,'mi')
- numtodsinterval(mod(EXTRACT(minute FROM cast(sysdate as timestamp)),10),'minute')
from dual;
甚至
code> select
trunc(sysdate,'mi')
- mod(EXTRACT(minute FROM cast(sysdate as timestamp)),10)/(24 * 60)
from双;
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
.
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;
这篇关于周期到10分钟间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!