oracle如何按天,需要Oracle SQL按天分割日期/时间范围

可以在SQL中执行此操作.有两个技巧.第一个是生成一系列数字,您可以使用连接进行CTE.

第二个是整合正确的逻辑来扩展日期,同时保持开始和结束的正确时间.

以下是一个例子：

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

Here它在SQLFiddle上.