Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp difference in minutes (Oracle 9.2.0.3)
Timestamp difference in minutes Wed, 16 December 2009 04:08
 Its_me_ved Messages: 979Registered: 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
 tahpush Messages: 961Registered: 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
 JRowbottom Messages: 5933Registered: 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
 Its_me_ved Messages: 979Registered: 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
 mnitu Messages: 159Registered: 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
 Michel Cadot Messages: 65138Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: Fri Aug 18 00:08:24 CDT 2017

Total time taken to generate the page: 0.19938 seconds