Oracle：如何跨天拆分記錄

我有兩個時間戳列 startdate 和 enddate 的數據。數據每天都在連續生成，有些記錄的開始日期是一天，結束日期是第二天午夜過後。例如：

原始記錄：

No.   StartDate                        EndDate
1    2014-01-10 20:50:04.45           2014-01-11 01:15:25.67

轉化記錄：

No.   StartDate                        EndDate
1     2014-01-10 20:50:04.45          2014-01-10 23:59:59.99
1     2014-01-11 00:00:00.00          2014-01-11 01:15:25.67

我如何在 Oracle 中執行此操作？

有幾種方法可以做到這一點。一種是在檢測到跨日條件時使用流水線函式返回附加行。另一種方法是複制行，然後消除不需要複製的行，並為需要複製的行調整時間。這是一個例子：

SELECT 
  Case When TwoRowsNeeded = 1 And RowNumber = 2 Then Trunc(EndDate)
       Else StartDate
  End StartDate,
  Case When TwoRowsNeeded = 1 AND RowNumber = 1 Then Trunc(EndDate)-1/24/60/60
       Else EndDate
  End EndDate
FROM
(
SELECT StartDate, EndDate
  , Row_Number() OVER (PARTITION BY StartDate, EndDate ORDER BY NULL) RowNumber
  , trunc(EndDate,'DD')-trunc(Startdate,'DD') TwoRowsNeeded
FROM t1
CROSS JOIN (select level from dual connect by level <=2)
)
WHERE RowNumber=1 Or TwoRowsNeeded=1;

設置：

drop table t1;
create table t1 as (
  select trunc(sysdate) StartDate, trunc(sysdate)+2/24 EndDate from dual
  UNION ALL
  select trunc(sysdate)-1, trunc(sysdate)-1+2/24 from dual
  UNION ALL
  select trunc(sysdate)-2/24, trunc(sysdate)-3/24 from dual
  UNION ALL
  select trunc(sysdate)-2/24, trunc(sysdate)+1/24 from dual
  UNION ALL
  select trunc(sysdate)-1/24/60, trunc(sysdate)+1/24/60 from dual
  );

SELECT to_char(StartDate,'MM/DD/YYYY HH24:MI:SS') StartDate
  , to_char(EndDate,'MM/DD/YYYY HH24:MI:SS') EndDate 
FROM t1;

聽起來像是數據庫觸發器的工作。假設 StartDate 和 EndDate 是靜態的（特定行的值始終保持不變），觸發器將相當簡單。如果在創建行後可以更新 EndDate，它仍然可以完成，但會涉及更多。這將處理只需要插入一行的地方；但是您可以放入一個循環來處理需要多於一行的情況（如果一行跨越兩天以上）。

假設表名為audit_log，我們創建一個名為的觸發器**t_audit_log**並註意“:NEW”。prefix 指定要插入的行的列的新值。為了簡單起見，我使用 DATE 數據類型，而不是 TIMESTAMP：

create or replace trigger t_audit_log
  before insert 
  on audit_log
begin
  if (trunc(:NEW.EndDate) > trunc(:NEW.StartDate)) then
       insert into audit_log (StartDate, EndDate) 
                      values (trunc(:NEW.StartDate)+1, :NEW.EndDate);
       :NEW.EndDate := trunc(:NEW.StartDate) + (86399/86400);
  end if;
end;
/

這將，如果截斷的EndDate（只是日期部分）大於截斷的 ，則在第二天的 00:00:00 和現有值的 中StartDate插入一行。然後它將目前記錄的 設置為 的 23:59:59 。audit_log``StartDate``EndDate``EndDate``EndDate``StartDate

例子：

SQLPLUS> create table audit_log (seq number, start_date date, end_date date);

Table created.

SQLPLUS>     create or replace trigger t_audit_log
    before insert
    on audit_log for each row
 begin
    if (trunc(:NEW.End_Date) > trunc(:NEW.Start_Date)) then
         insert into audit_log (Seq, Start_Date, End_Date)
                        values (:NEW.Seq, trunc(:NEW.Start_Date)+1, :NEW.End_Date);
         :NEW.End_Date := trunc(:NEW.Start_Date) + (86399/86400);
    end if;
 end;
 /

Trigger created.

SQLPLUS> insert into audit_log (seq, start_date, end_date)
  values (1, to_date('25-Oct-14 00:00:00'), to_date('25-Oct-14 23:00:00'));

1 row created.

SQLPLUS> insert into audit_log (seq, start_date, end_date)
   values (2, to_date('26-Oct-14 00:00:00'), to_date('26-Oct-14 23:59:59'));

1 row created.

SQLPLUS> insert into audit_log (seq, start_date, end_date)
 values (3, to_date('27-Oct-14 01:02:03'),  to_date('27-Oct-14 02:03:04'));

1 row created.

SQLPLUS> insert into audit_log (seq, start_date, end_date)
   values (4, to_date('27-Oct-14 01:02:03'),  to_date('28-Oct-14 02:03:04'));

1 row created.

SQLPLUS> insert into audit_log (seq, start_date, end_date)
   values (5, to_date('10-Jan-14 20:50:04'), to_date('11-Jan-14 01:15:25'));

1 row created.

SQLPLUS> select * from audit_log order by seq, start_date;

      SEQ START_DATE         END_DATE
---------- ------------------ ------------------
        1 25-Oct-14 00:00:00 25-Oct-14 23:00:00
        2 26-Oct-14 00:00:00 26-Oct-14 23:59:59
        3 27-Oct-14 01:02:03 27-Oct-14 02:03:04
        4 27-Oct-14 01:02:03 27-Oct-14 23:59:59
        4 28-Oct-14 00:00:00 28-Oct-14 02:03:04
        5 10-Jan-14 20:50:04 10-Jan-14 23:59:59
        5 11-Jan-14 00:00:00 11-Jan-14 01:15:25

7 rows selected.

SQLPLUS>

引用自：https://dba.stackexchange.com/questions/80044