OraFAQ Forum: SQL & PL/SQL » Differerence between timestamp

Home » SQL & PL/SQL » SQL & PL/SQL » Differerence between timestamp

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Differerence between timestamp [message #262650]

Tue, 28 August 2007 00:32

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?

Report message to a moderator

Re: Differerence between timestamp [message #262660 is a reply to message #262650]

Tue, 28 August 2007 00:52

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Differerence between timestamp [message #262686 is a reply to message #262650]

Tue, 28 August 2007 01:22

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 14:56:42 CST 2024