日期:2014-05-17  浏览次数:20743 次

如何时间以15分钟归类
现有时间点如下,我要以15分钟为一个节点进行归类,一天24小时即有96个节点,该如何归类?
例如:
2010-6-3 上午 03:03:00
2010-6-3 上午 03:43:00
2010-6-3 上午 10:03:00
2010-6-3 上午 10:53:00
应该归为
2010-6-3 上午 03:00:00
2010-6-3 上午 03:30:00
2010-6-3 上午 10:00:00
2010-6-3 上午 10:45:00
如何实现?

------解决方案--------------------
小时*60+分钟/15 得到当前的节点....
------解决方案--------------------
例如
SQL code
select  extract(HOUR FROM TIMESTAMP '2010-10-10 9:10:10') as 小时,
extract(MINUTE FROM TIMESTAMP '2010-10-10 9:10:10') as 分钟 ,
ceil((extract(HOUR FROM TIMESTAMP '2010-10-10 9:10:10')*60+extract(MINUTE FROM TIMESTAMP '2010-10-10 9:10:10'))/15) as 节点 
from dual;

------解决方案--------------------
SQL code

select to_char(sysdate, 'yyyymmdd hh24:mi:ss') dt,BASE_TAB.node
from (
select 
to_char(trunc(sysdate) + interval '15' minute * (level - 1),'hh24mi') bt,
to_char(trunc(sysdate) + interval '15' minute * (level) - interval '1' minute,'hh24mi') et,
level node
from dual
connect by level <= 96
) base_tab, dual
where to_char(sysdate, 'hh24mi') between base_tab.bt and base_tab.et

DT                              NODE
------------------------- ----------
20100603 15:12:39                 61

------解决方案--------------------

select sysdate,trunc(sysdate,'hh')+floor(to_char(sysdate,'mi')/15)*15/60/24 from dual;

-------------
2010-6-3 19:54:34 2010-6-3 19:45:00