0

I have data with two timestamp columns startdate and enddate. The data is produced continuously everyday and some records are such that the startdate is one day and the enddate is the nextday as it passes midnight. For example:

Original Record:

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

Transformed Record:

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

How do I do this in Oracle?

  • As much as I don't like asking this, I think it needs asking... why do you need to do this? It seems like an awful lot of work for probably not much gain. – Phil Sumner Oct 14 '14 at 8:59
  • @PhilSumner I wouldn't have asked if it wasn't needed now would I? – sfactor Oct 14 '14 at 9:14
1

There are several ways to do this. One would be to use a pipelined function to return the additional row when the day spanning condition is detected. Another way is to duplicate the rows and then eliminate the ones that don't need to be duplicated and adjust the times for those that do. Here is an example:

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;

Setup:

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;
  • It looks like you're using timestamps rather than dates. The solution will work, but the date calculations will need to be modified. – Leigh Riffel Oct 28 '14 at 18:33
0

Sounds like a job for a database trigger. Assuming the StartDate and EndDate are static (the values remain the same for a particular row always), a trigger would be fairly simple. If the EndDate could be updated after the row is created, it could still be done but would be more involved. This would handle where just one additional row would need to be inserted; but you could put a loop in to handle cases where more than one row would be needed (if a row spanned more than two days).

Assume the table is named audit_log, we create a trigger called t_audit_log and note that the ":NEW." prefix specifies the new value of the columns for the row being inserted. To make it simpler, I use a datatype of DATE, not 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;
/

That will, if the truncated EndDate (just the date portion) is larger than the truncated StartDate, insert a row into audit_log with the StartDate of the next day at 00:00:00 and an EndDate of the existing value of EndDate. It then sets the EndDate of the current record to be at 23:59:59 of the StartDate.

Example:

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.