Oracle,将持续时间行除以一小时 [英] Oracle, split a time duration row by one hour period
问题描述
我正在研究oracle db,但我不太擅长Oracle. 我正在尝试按一个小时的时间段分隔行.
I'm working on oracle db and I'm not quite good at Oracle. I'm trying to split a row by one hour period.
例如,如果一个时间行如下所示,
For example, if a time row has given as below,
开始时间(yyyy/mm/dd hh24:mi:ss)|结束时间(yyyy/mm/dd hh24:mi:ss)
Start_time(yyyy/mm/dd hh24:mi:ss) | End_time(yyyy/mm/dd hh24:mi:ss)
2013/09/01 09:30:00 2013/09/01 11:38:59
我想要看到的结果:
2013/09/01 09:30:00 2013/09/01 09:59:59
2013/09/01 10:00:00 2013/09/01 10:59:59
2013/09/01 11:00:00 2013/09/01 11:38:59
我已经被搜索过如何做,但找不到.
但是我想可以通过使用"CONNECT BY"来完成.
任何帮助将是巨大的.提前致谢.
I've been searched how to do it but I couldn't find one.
But I guess It can be done by using 'CONNECT BY'.
Any help will be great. Thanks in advance.
推荐答案
我有一个基本查询,您可以解决该问题并获得所需的内容.
I have bulit a basic query, you can work around it and get what you want.
select greatest(Start_time, trunc(Start_time+(level-1)/24, 'hh24')),
least(End_time, trunc(Start_time+(level)/24, 'hh24'))
from log_table
connect by level <= floor((dt1-dt2)*24)+1;
sqlfiddle示例:
Example at sqlfiddle:
http://sqlfiddle.com/#!4/82625/29
这篇关于Oracle,将持续时间行除以一小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!