Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp difference in minutes (Oracle 9.2.0.3)
Timestamp difference in minutes [message #435426] Wed, 16 December 2009 04:08 Go to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Is there any apropriate way to get the timestamp difference in minutes? I wrote this way.
Method1:
 SELECT SUBSTR(T,1,30) "T",
       SUBSTR(T1,1,30) "T1",
     TRUNC( ( TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+7,2)) +
       TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+4,2)*60) +
       TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+1,2)*3600))/60) R      
  FROM TEST_111;



SQL>  SELECT SUBSTR(T,1,30) "T",
  2         SUBSTR(T1,1,30) "T1",
  3       TRUNC( ( TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+7,2)) +
  4         TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+4,2)*60) +
  5         TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+1,2)*3600))/60) R      
  6    FROM TEST_111;

T                              T1                                      R
------------------------------ ------------------------------ ----------
15-DEC-09 05.00.42.437000 AM   16-DEC-09 05.01.54.984000 PM          721

or,
SQL> SELECT SUBSTR(T,1,30) "T",
  2         SUBSTR(T1,1,30) "T1",
  3       TRUNC( TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+4,2)) +
  4         TO_NUMBER(SUBSTR((T1-T), INSTR((T1-T),' ')+1,2)*60)) R      
  5    FROM TEST_111;

T                              T1                                      R
------------------------------ ------------------------------ ----------
15-DEC-09 05.00.42.437000 AM   16-DEC-09 05.01.54.984000 PM          721


Method 2:
SQL>  select 
  2     ( (extract(hour from t1)-extract(hour from t))*60+
  3      (extract(minute from t1)-extract(minute from t))) from test_111;

((EXTRACT(HOURFROMT1)-EXTRACT(HOURFROMT))*60+(EXTRACT(MINUTEFROMT1)-EXTRACT(MINU
--------------------------------------------------------------------------------
                                                                             721

[Updated on: Wed, 16 December 2009 04:20]

Report message to a moderator

Re: Timestamp difference in minutes [message #435436 is a reply to message #435426] Wed, 16 December 2009 04:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

SELECT ROUND(TO_NUMBER(CAST (SYSTIMESTAMP AS DATE)
             - CAST (TIMESTAMP '2009-12-16 11:30:00' AS DATE))
             * 24* 60)
  FROM DUAL;
Re: Timestamp difference in minutes [message #435444 is a reply to message #435426] Wed, 16 December 2009 04:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What's wrong with using EXTRACT on the difference between the two:
SQL> with src as (select systimestamp+(3/(24*60)) dte1,systimestamp dte2 from dual)
  2  select extract(minute from dte1-dte2) diff
  3        ,dte1-dte2
  4        ,dte1
  5        ,dte2 
  6  from   src;

      DIFF DTE1-DTE2                      DTE1      DTE2
---------- ------------------------------ --------- --------------------------------------
         2 +000000000 00:02:59.738000     16-DEC-09 16-DEC-09 10.38.08.262000 AM +00:00
Re: Timestamp difference in minutes [message #435448 is a reply to message #435426] Wed, 16 December 2009 05:10 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Thanks you!
Re: Timestamp difference in minutes [message #435529 is a reply to message #435444] Wed, 16 December 2009 10:01 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
JRowbottom wrote on Wed, 16 December 2009 04:39
What's wrong with using EXTRACT on the difference between the two:
SQL> with src as (select systimestamp+(3/(24*60)) dte1,systimestamp dte2 from dual)
  2  select extract(minute from dte1-dte2) diff
  3        ,dte1-dte2
  4        ,dte1
  5        ,dte2 
  6  from   src;

      DIFF DTE1-DTE2                      DTE1      DTE2
---------- ------------------------------ --------- --------------------------------------
         2 +000000000 00:02:59.738000     16-DEC-09 16-DEC-09 10.38.08.262000 AM +00:00


Maybe ORA-30076 ?

Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> with src as (select systimestamp +(3/(24*60)) dte1, systimestamp-1  dte2 from dual)
  2    select extract(minute from dte1-dte2) diff
  3          ,dte1-dte2
  4          ,dte1
  5          ,dte2
  6    from   src
  7  /
 
with src as (select systimestamp +(3/(24*60)) dte1, systimestamp-1  dte2 from dual)
  select extract(minute from dte1-dte2) diff
        ,dte1-dte2
        ,dte1
        ,dte2
  from   src
 
ORA-30076: champ d'extraction non valide pour la source d'extraction
 
SQL> 
Re: Timestamp difference in minutes [message #435538 is a reply to message #435529] Wed, 16 December 2009 10:28 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQB> l
  1  with src as (select cast(systimestamp +(3/(24*60)) as timestamp) dte1,
  2                      cast(systimestamp-1 as timestamp) dte2
  3               from dual)
  4    select extract(minute from dte1-dte2) diff
  5          ,dte1-dte2 diff2
  6          ,dte1
  7          ,dte2
  8*   from   src
SQB> /
      DIFF DIFF2                      DTE1                      DTE2
---------- -------------------------- ------------------------- -------------------------
         3 +000000001 00:03:00.000000 16/12/2009 17:31:29.000   15/12/2009 17:28:29.000

1 row selected.

Regards
Michel
Previous Topic: sql query join
Next Topic: Compare output files
Goto Forum:
  


Current Time: Tue Sep 27 14:31:29 CDT 2016

Total time taken to generate the page: 0.19527 seconds