Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    46

    Unanswered: Difference between timestamps

    Hello,

    I am working in Oracle 9i SQL. I want to find the difference between two timestamps which returns me the minutes difference.

    can u plz. give me a solution for this.

    Thanks

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    TIMESTAMP - TIMESTAMP => INTERVAL
    DATE - DATE => NUMBER OF DAYS

    Maybe there are other ways, but by converting Timestamps to Date you will have the difference in number of days, which you can convert easily to number of minutes :

    Code:
    SELECT 
       ROUND
       (
          TO_NUMBER
             (
                TO_DATE(TO_CHAR(SYSTIMESTAMP, 'DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS') 
                -
                TO_DATE(TO_CHAR(TO_TIMESTAMP('12052006:15:47:21','DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS')
             ) * 24 * 60
       ) "DIFFERENCE IN MINUTES"
    FROM DUAL;
    Code:
    rbaraer@Ora10g> SELECT
       ROUND
      2    3     (
      4        TO_NUMBER
      5           (
      6              TO_DATE(TO_CHAR(SYSTIMESTAMP, 'DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS')
      7              -
      8              TO_DATE(TO_CHAR(TO_TIMESTAMP('12052006:15:47:21','DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS'), 'DDMMYYYY:HH24:MI:SS')
      9           ) * 24 * 60
     10     ) "DIFFERENCE IN MINUTES"
     11  FROM DUAL;
    
    DIFFERENCE IN MINUTES
    ---------------------
                       47
    
    rbaraer@Ora10g>
    I work with 10g but I think it will work with 9i.

    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    SELECT FLOOR(((TIMESTAMP1 - TIMESTAMP2) DAY TO SECOND) / 60) from MYTABLE;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Beilstwh, your solution does not seem to work :

    Code:
    rbaraer@Ora10g> SELECT
       FLOOR
       (
          (
            (SYSTIMESTAMP
            -
            TO_TIMESTAMP('12052006:15:47:21','DDMMYYYY:HH24:MI:SS'))
            DAY TO SECOND
          ) / 60
       ) "DIFFERENCE IN MINUTES"
    FROM DUAL;  2    3    4    5    6    7    8    9   10   11
          ) / 60
            *
    ERROR at line 9:
    ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL
    
    
    rbaraer@Ora10g>
    I've seen functions to convert from number to interval (NUMTODSINTERVAL...) but none to do the contrary... Is it possible ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    btw I find CAST is a convenient way to convert timestamps to dates:

    Code:
    SELECT ROUND
           ( TO_NUMBER
             ( CAST(SYSTIMESTAMP AS DATE) -
               CAST(TIMESTAMP '2006-05-12 15:47:21' AS DATE)
             ) * 24 * 60
            ) "DIFFERENCE IN MINUTES"
    FROM   dual;

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by WilliamR
    btw I find CAST is a convenient way to convert timestamps to dates:

    Code:
    SELECT ROUND
           ( TO_NUMBER
             ( CAST(SYSTIMESTAMP AS DATE) -
               CAST(TIMESTAMP '2006-05-12 15:47:21' AS DATE)
             ) * 24 * 60
            ) "DIFFERENCE IN MINUTES"
    FROM   dual;
    You're right : I find your "CAST" solution much more elegant than mine, I should use that more often . Just a question : is the string format
    Code:
    'YYYY-MM-DD HH24:MI:SS'
    used in "TIMESTAMP '2006-05-12 15:47:21'" universal or does it depend on some parameter ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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