oracle的时间范围,需要Oracle SQL按日期划分日期/时间范围

小编典典

可以在SQL中执行此操作。有两个技巧。首先是生成一系列数字，您可以使用进行CTE处理connect。

第二个是将正确的逻辑放在一起以扩展日期，同时保留正确的开始和结束时间。

以下是一个示例：

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。

2021-03-10