How to get the duration between the two TIMESTAMOS??? 1

[babe1898]

Hi all,

I'm pretty new to SQL so I only know some of the basic stuffs. I created two columns BEGIN and END both have a TIMESTAMP as datatype. Now. my question is how could I get the diference between these two times/date, meaning the duration between the BEGIN and the END. For example is shown below

Begin
15-NOV-07 08.52.00.000000 AM

End
16-NOV-07 08.58.01.000000 AM

Could you please give some ideas or is/are there any functions that can be used? I'm using ORACLE 10 as a databse. Thank you in advance.



Cordially,

Babe1898

 

[babe1898]

Sorry, it's TIMESTAMP. Thanks.

 

[taupirho]

1* select to_timestamp('16-NOV-07 08.58.01.000000 AM') - to_timestamp('15-NOV-
07 08.52.00.000000 AM') from dual
SQL> /

TO_TIMESTAMP('16-NOV-0708.58.01.000000AM')-TO_TIMESTAMP('15-NOV-0708.52.00.
---------------------------------------------------------------------------
+000000001 00:06:01.000000000

1 day 6 minutes and 1 minute I beleive




In order to understand recursion, you must first understand recursion.

 

[babe1898]

Hi taupirho,

Thank you so much for your help. It worked like a charm. It worked for most. But one thing though, does this method has a bug? I tried the following(Ex.1) and it did not give the correct answer. I'm using ORACLE 10g. Do you mind running the following statement and check if you get the same results? Thank you in advance...

Ex.1
select to_timestamp('16-NOV-07 12.00.00.000000 AM') - to_timestamp('16-NOV-07 11.59.00.000000 AM') from dual

TO_TIMESTAMP('16-NOV-0712.00.00.000000AM')-TO_TIMESTAMP('16-NOV-0711.59.00.000000AM')
-------------------------------------------------------------------------------------
0 -11:-59:0.0

1 rows selected

 

[taupirho]

Its not a bug, 12.00 AM is midnight (i.e start of the first minute of the 16th)

11.59 is one minute to noon (on the 16th).

In my mind this is 11 hours and 59 minutes


In order to understand recursion, you must first understand recursion.