我想将我的数据汇总到15分钟段(一小时的季度).为此,我编写了一些生成15分钟日期时间块的代码.
SELECT TRUNC(SYSDATE,'hh') + 0.25/24 - (ROWNUM) *0.25/ 24 AS time_start,ROWNUM,TRUNC(SYSDATE,'hh') + 0.25/24 - (ROWNUM - 1) *0.25/ 24 AS time_end FROM widsys.consist WHERE ROWNUM <3000 ORDER BY sysdate
我的代码的问题是因为它使用了一个小时截断,它只会从最近一小时的开始生成时间戳.例如,现在是11:49 AM,因此生成的第一个标记是11:00 AM.
这将为您提供最近的季度.
原文链接：https://www.f2er.com/oracle/204879.htmlselect sysdate,trunc(sysdate,'mi') - --truncate to the nearest minute
numtodsinterval( --convert the minutes in number to interval type and subtract.
mod(to_char(sysdate,'mi'),15),--find the minutes from the nearest quarter
'minute'
) as nearest_quarter
from dual;
输出：
sysdate nearest_quarter ----------------------------------------------------------------- October,11 2013 05:54:24+0000 October,11 2013 05:45:00+0000 October,11 2013 05:22:24+0000 October,11 2013 05:15:00+0000
使用它作为起始值,然后迭代它.
with cte as(
select trunc(sysdate,'mi') -
numtodsinterval(mod(to_char(sysdate,'minute') as nearest_quarter
from dual
)
select nearest_quarter - numtodsinterval((level - 1)*15,'minute'),nearest_quarter - numtodsinterval((level - 2)*15,'minute')
from cte
connect by level <= 10;
Demo.
