Home » SQL & PL/SQL » SQL & PL/SQL » Differerence between timestamp
Differerence between timestamp [message #262650] Tue, 28 August 2007 00:32 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I have two timestamp data types. How I can find out the difference between two timestamnp data in minutes?
Re: Differerence between timestamp [message #262660 is a reply to message #262650] Tue, 28 August 2007 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> def t1="to_timestamp('28/08/2007 07:20:58.458','DD/MM/YYYY HH24:MI:SS.FF3')"
SQL> def t2="to_timestamp('28/08/2007 07:48:54.955','DD/MM/YYYY HH24:MI:SS.FF3')"
SQL> col t1 format a23 trunc
SQL> col t2 format a23 trunc
SQL> col d format a23 trunc
SQL> select &t1 t1, &t2 t2, &t2-&t1 d from dual;
T1                      T2                      D
----------------------- ----------------------- -----------------------
28/08/2007 07:20:58.458 28/08/2007 07:48:54.955 +000000000 00:27:56.497

1 row selected.

SQL> with data as (select &t2-&t1 d from dual)
  2  select extract(minute from d)+extract(second from d)/60 min
  3  from data
  4  /
       MIN
----------
27.9416167

1 row selected.

Regards
Michel
Re: Differerence between timestamp [message #262686 is a reply to message #262650] Tue, 28 August 2007 01:22 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Thank you Michel so much. It worked.

I want to add while experiment if date is different then add day and hours to get difference between two dates in minutes.

select extract(day from a-b)*24*60
       + extract (hour from a-b)*60
       + extract(minute from a-b) 
       + extract(second from a-b)/60 min 
from test;

Thank you Again.

[Updated on: Tue, 28 August 2007 01:24] by Moderator

Report message to a moderator

Re: Differerence between timestamp [message #262690 is a reply to message #262686] Tue, 28 August 2007 01:28 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please take care of line length.
If you don't care about the subsecond data then it is easier to convert to date:
SQL> select &t1 t1, &t2 t2, (&t2+0)-(&t1+0) "DAY", ((&t2+0)-(&t1+0))*24*60 min 
  2  from dual;
T1                      T2                             DAY        MIN
----------------------- ----------------------- ---------- ----------
28/08/2007 07:20:58.458 28/08/2007 07:48:54.955 .019398148 27.9333333

1 row selected.

Regards
Michel

Previous Topic: how to get two tables from two different oracle databases to perform join
Next Topic: retriving xml set (merged)
Goto Forum:
  


Current Time: Sun Dec 04 22:35:08 CST 2016

Total time taken to generate the page: 0.13443 seconds