1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to compare two dates, substract them and add seconds?

Discussion in 'SQL PL/SQL' started by ivo.kolev, Jul 14, 2009.

  1. ivo.kolev

    ivo.kolev Guest

    Hello all,

    I am fairly a newbie to Oracle but have to write a script that compares dates.
    I searched in the internet and found some useful code, however one thing I could not imagine is how to specify different values and subtract them.

    Say I have following table:

    Code (Text):
    object      event_time                    duration(sec)

    o1            14.07.09 15:22:35           3450
    o2            14.07.09 15:43:02             360
    I need to compare both dates, establish which is later, then add duration to one which is earlier and see what happens : still earlier or later. It might be a piece of cake for everybody but me.

    I do not know how to index the event_time of o1 and o2 in order to compare them?

    Someone please give me a hint!

    Thank you in advance
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also use GREATEST and LEAST functions to find the latest and earliest dates respectively ..
     
  4. krishnaiah

    krishnaiah Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Use to_char function in SQL.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I'm not certain what it is, exactly, that you want but here are two examples using LAG that might help:

    Code (SQL):
    SQL> CREATE TABLE timediff(
      2          obj     varchar2(12),
      3          event_time DATE,
      4          duration  NUMBER
      5  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO timediff
      3  VALUES('o1',to_date('14.07.09 15:22:35','DD.MM.YY HH24:MI:SS'), 3450)
      4  INTO timediff
      5  VALUES('o2',to_date('14.07.09 15:43:02','DD.MM.YY HH24:MI:SS'), 360)
      6  SELECT * FROM dual;
     
    2 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT obj, event_time, prev_event_time, duration, prev_event_time + (duration/86400) adj_event_time
      2  FROM
      3  (SELECT obj, event_time, lag(event_time) OVER (ORDER BY event_time) prev_event_time,
      4          duration
      5   FROM timediff)
      6  /
     
    OBJ          EVENT_TIME        PREV_EVENT_TIME     DURATION ADJ_EVENT_TIME
    ------------ ----------------- ----------------- ---------- -----------------
    o1           14.07.09 15:22:35                         3450
    o2           14.07.09 15:43:02 14.07.09 15:22:35        360 14.07.09 15:28:35
     
    SQL>
    SQL> SELECT obj, event_time, prev_event_time, duration, prev_event_time + (greatest(duration, prev_duration)/86400) adj_event_time
      2  FROM
      3  (SELECT obj, event_time, lag(event_time) OVER (ORDER BY event_time) prev_event_time,
      4          duration, lag(duration) OVER (ORDER BY event_time) prev_duration
      5   FROM timediff)
      6  /
     
    OBJ          EVENT_TIME        PREV_EVENT_TIME     DURATION ADJ_EVENT_TIME
    ------------ ----------------- ----------------- ---------- -----------------
    o1           14.07.09 15:22:35                         3450
    o2           14.07.09 15:43:02 14.07.09 15:22:35        360 14.07.09 16:20:05
     
    SQL>