Home » SQL & PL/SQL » SQL & PL/SQL » difference between timestamp
difference between timestamp [message #217100] Wed, 31 January 2007 12:14 Go to next message
aline
Messages: 92
Registered: February 2002
Member
Hello,

I want to get the substraction between to timestamp in day and fractional day. the result should be in the same type than the difference between to date.
Soemone now if there is an elegant way to do that?
I could use multiple extract and convert it into number, but it will be very heavy. I'll like a lighting way Smile

[Updated on: Wed, 31 January 2007 12:14]

Report message to a moderator

Re: difference between timestamp [message #217105 is a reply to message #217100] Wed, 31 January 2007 12:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
select (end_date - begin_date) from my_table;
Re: difference between timestamp [message #217221 is a reply to message #217100] Thu, 01 February 2007 04:08 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
ok,
Thk for the help

So, my problem is not the query but the type of the result.
With date, the type is in number. For example if there is 12 hours between end_date and begin_date, your query will return 0.5.
But if I'll upgrade the format of my column, the result will be an interval. This is not very good for compatibility.
So, I want to substract two timestamp with time zone and have a result in number!

thk again
Re: difference between timestamp [message #217238 is a reply to message #217221] Thu, 01 February 2007 05:04 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You may want to check this thread.
Re: difference between timestamp [message #217298 is a reply to message #217100] Thu, 01 February 2007 10:02 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
Thk,

This is exactly what I wanted.
So I have to wait the 11G server to have something nice!
The solution in the other post is good but not the best of course.
I have to multiply my result by (24*60*60), translate it in number and divided it by the same number!


to_number(substr((((a.my_tmstp_wlcltz-b.my_tmstp_wlcltz)*(24*60*60))),1,10))/(3600*24),
Re: difference between timestamp [message #217301 is a reply to message #217100] Thu, 01 February 2007 10:31 Go to previous message
aline
Messages: 92
Registered: February 2002
Member
The result is FALSE!
Oracle round the result in date and it will be false!

For exemple, if you try:

SQL>  select (systimestamp+1)-systimestamp diff from dual;

DIFF
-------------------
+000000000 23:59:59


so you havn't the good result and if you want for exemple to convert it in day you'll have:

SQL> select to_number(substr((((systimestamp+1)-systimestamp)*(24*3600)),1,10)/(3600*24)) from dual;

TO_NUMBER(SUBSTR((((SYSTIMESTA
------------------------------
             0.287673611111111




wich is totally false.

Another solution is to convert manually the timestamp into date but I don't know if it'll work with local time zone!
select (systimestamp+1) - (systimestamp+0) from dual;
(SYSTIMESTAMP+1)-(SYSTIMESTAMP
------------------------------
                             1




Previous Topic: Sequence no longer exists....
Next Topic: Get Next ID
Goto Forum:
  


Current Time: Sat Dec 10 08:51:20 CST 2016

Total time taken to generate the page: 0.05851 seconds