Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    6

    Unanswered: calculating the difference between days along with timestamp

    Hi All,

    I need help in calculating the difference between days along with timestamp.
    i.e, for eg I have
    x= 22:20:15:10(Days:Hours:Mins : Secs)
    and
    y= 34:10:58:10(Days:Hours:Mins : Secs)

    Now I need to find the difference between y and x, i.e. (y-x)
    Please do suggest me how to acquire the result.


    Thanks & Regards,
    Kashyap

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    SQL> create table test
      2    (x date,
      3     y date
      4    );
    
    Table created.
    
    SQL> insert into test (x, y)
      2    value
      3
    SQL> insert into test (x, y) values (to_date('01.03.2012 14:30:15', 'dd.mm.yyyy hh24:mi:ss'),
      2                                  to_date('10.03.2012 23:45:50', 'dd.mm.yyyy hh24:mi:ss'));
    
    1 row created.
    
    SQL> select * from test;
    
    X                   Y
    ------------------- -------------------
    01.03.2012 14:30:15 10.03.2012 23:45:50
    
    SQL> select y - x result
      2  from test;
    
        RESULT
    ----------
    9,38582176
    
    SQL>
    The result is number of DAYS. It means that you'll need to use some arithmetics to convert it to another format (you know ... a day has 24 hours, an hour has 60 minutes, and so forth).

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Wink

    I hope you are using the formal interval datatype for this: INTERVAL DAY TO SECOND datatype

    Anyway, an alternative to @LittleFoot's example which would not require you to convert your interval to a date:
    Code:
    dayneo@SANDBOX> declare
      2  
      3  	     l_x interval day to second (0);
      4  	     l_y interval day to second (0);
      5  	     l_z interval day to second (0);
      6  
      7  begin
      8  
      9  	     l_x := INTERVAL '+22 20:15:10' day to second;
     10  	     l_y := INTERVAL '+34 10:58:10' day to second;
     11  	     l_z := l_y - l_x;
     12  
     13  	     dbms_output.put_line(l_z);
     14  
     15  end;
     16  /
    +11 14:43:00
    
    PL/SQL procedure successfully completed.
    
    dayneo@SANDBOX>
    Note that the output is returned as days hours minutes seconds automatically... no additional calculations required. You can also now use this for any other date based arithmetic.

    It is worth noting that Oracles interval format is different from the one you are using. I would recommend you change your format to comply with Oracles. It will just make such things that much easier for you.

    Read this: Not quite your case, but relevant to storing timestamps/intervals as strings

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •