需要Oracle SQL按日期划分日期/时间范围 [英] Need Oracle SQL to split up date/time range by day
问题描述
我希望能得到一些帮助,以帮助我编写一些我自己编写的SQL程序.
I am hoping to get some help to write some SQL that I have had no success writing myself.
我有一张数据表:
ID StartDate EndDate
1 01/01/2000 04:30 PM 01/03/2000 06:15 AM
2 01/04/2000 08:10 AM 01/04/2000 07:25 AM
3 01/05/2000 11:00 AM 01/06/2000 03:45 AM
我需要获得以下信息:
ID StartDate EndDate
1 01/01/2000 04:30 PM 01/01/2000 11:59 PM
1 01/02/2000 12:00 AM 01/02/2000 11:59 PM
1 01/03/2000 12:00 AM 01/03/2000 06:15 AM
2 01/04/2000 08:10 AM 01/04/2000 07:25 AM
3 01/05/2000 11:00 AM 01/05/2000 11:59 PM
3 01/06/2000 12:00 AM 01/06/2000 03:45 AM
换句话说,按日期划分日期范围.在SQL中甚至有可能吗?
In other words, split up date ranges by day. Is this even possible in SQL?
我的数据库是Oracle 11G R2,由于某些情况,我担心不能使用PL/SQL.
My database is Oracle 11G R2 and I am afraid due to circumstances I cannot use PL/SQL.
推荐答案
可以在SQL中执行此操作.有两个技巧.首先是生成一系列数字,您可以使用connect
对CTE进行处理.
It is possible to do this in SQL. There are two tricks. The first is generating a series of numbers, which you can do with a CTE using connect
.
第二个是将正确的逻辑放在一起以扩展日期,同时保留正确的开始和结束时间.
The second is putting together the right logic to expand the dates, while keeping the right times for the beginning and end.
以下是示例:
with n as (
select level n
from dual connect by level <= 20
),
t as (
select 1 as id, to_date('01/01/2000 4', 'mm/dd/yyyy hh') as StartDate, to_date('01/03/2000 6', 'mm/dd/yyyy hh') as EndDate from dual union all
select 2 as id, to_date('01/04/2000 8', 'mm/dd/yyyy hh') as StartDate, to_date('01/04/2000 12', 'mm/dd/yyyy hh') as EndDate from dual union all
select 3 as id, to_date('01/05/2000', 'mm/dd/yyyy') as StartDate, to_date('01/06/2000', 'mm/dd/yyyy') as EndDate from dual
)
select t.id,
(case when n = 1 then StartDate
else trunc(StartDate + n - 1)
end) as StartDate,
(case when trunc(StartDate + n - 1) = trunc(enddate)
then enddate
else trunc(StartDate + n)
end)
from t join
n
on StartDate + n - 1 <= EndDate
order by id, StartDate
此处它位于SQLFiddle上.
Here it is on SQLFiddle.
这篇关于需要Oracle SQL按日期划分日期/时间范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!