Oracle PLSQL将日期时间截断为15分钟 [英] Oracle PLSQL truncate datetimes to 15 min blocks

问题描述

我想将我的数据汇总到15分钟的片段中(每小时的四分之一).为此，我编写了一些代码，该代码生成15分钟的日期时间块.

I would like to aggregate my data into 15 minute segments (quarters of the hour). To do this, I have written some code that generates 15 minute datetime blocks.

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.

The problem with my code is because it uses an hour truncation, it will only generate time stamps from the beginning of the most recent hour. For example, it is 11:49AM now so the first stamp generated is 11:00AM.

我需要它从最后15分钟的代码块开始(上例中的 11:45 AM )开始生成戳记.谁能帮我吗?

I need it to generate stamps from the beginning of the last 15 minute block (11:45AM from the example above). Can anyone please help me?

推荐答案

这将为您提供最近的一个季度.

This will get you the nearest quarter.

select 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

使用此值作为起始值，然后遍历该值.

Use this as your starting value and then iterate over this.

with cte as(
  select trunc(sysdate,'mi') - 
         numtodsinterval(mod(to_char(sysdate,'mi'),15),'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;

演示.

这篇关于Oracle PLSQL将日期时间截断为15分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！