Home » SQL & PL/SQL » SQL & PL/SQL » Date Calculation with Oracle
Date Calculation with Oracle [message #341201] Sun, 17 August 2008 20:19 Go to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hi,

I have some ideas that you can do some basic data calculation with Oracle query. Is it possible to accomplish something like this?

I would like to calculate the different hours between a list of given time stamp.
 08/01/2008 00:00  1200 
                     --14 hours different

 08/01/2008 14:00  1400 \  
                         --33 hours different
 08/02/2008 23:00  1600 /

output:
 08/01/2008 00:00  14
 08/01/2008 14:00  33
 08/02/2008 23:00   1 





[Updated on: Sun, 17 August 2008 20:23]

Report message to a moderator

Re: Date Calculation with Oracle [message #341202 is a reply to message #341201] Sun, 17 August 2008 20:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I would like to calculate the different hours between a list of given time stamp.
http://www.orafaq.com/forum/t/88153/0/
We would like you to read & FOLLOW the Posting Guidelines as stated in URL above
Re: Date Calculation with Oracle [message #341205 is a reply to message #341202] Sun, 17 August 2008 20:54 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member

SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1,
TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2,
trunc(((86400*(date2-date1))/60)/60)-
24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours,
FROM dual



My problem is that my table is structured as follow with only one date column.


user_id login_date
23 08/01/2008 00:00
23 08/01/2008 14:00
23 08/02/2008 23:00

Re: Date Calculation with Oracle [message #341213 is a reply to message #341201] Sun, 17 August 2008 21:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>My problem is that my table is structured as follow with only one date column.
Table? what table? I do not see any table.
So? Specify the same table twice in the FROM clause.

Also Oracle does NOT guarentee the order of returned rows without the use of ORDER BY clause.
Re: Date Calculation with Oracle [message #341224 is a reply to message #341213] Sun, 17 August 2008 21:44 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member


Here's my attempts to solve the problem without success... I don't thing I get the right column for the calculation.

select a.date_time date1 ,b.date_time date2, date2-date1 from test a,test b
where a.date_time = b.date_time
order by date_time

Re: Date Calculation with Oracle [message #341226 is a reply to message #341201] Sun, 17 August 2008 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
if a.date_time = b.date_time , then date2-date1 = ZERO

Since you are unwilling or incapable to follow Posting Guidelines, You're On Your Own (YOYO)!
Re: Date Calculation with Oracle [message #341228 is a reply to message #341201] Sun, 17 August 2008 22:45 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Maybe you will find the answer in this thread: http://www.orafaq.com/forum/t/123339/96705/

By the way, why shall be the last value 1?
Re: Date Calculation with Oracle [message #341239 is a reply to message #341228] Sun, 17 August 2008 23:28 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Thanks for your help... It may just help me to get start it.

It's just a typo. The last value is should not be 1.
Re: Date Calculation with Oracle [message #341421 is a reply to message #341239] Mon, 18 August 2008 19:17 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
No Message Body

[Updated on: Tue, 19 August 2008 10:58]

Report message to a moderator

Re: Date Calculation with Oracle [message #341433 is a reply to message #341201] Mon, 18 August 2008 19:50 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>The results seem odd.
Odd in what way?

>Can someone please look at my query?
You got what you requested.
Re: Date Calculation with Oracle [message #341444 is a reply to message #341433] Mon, 18 August 2008 20:53 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
I'm trying to understand why the output is in decimal format. How would I convert to hours?

Re: Date Calculation with Oracle [message #341450 is a reply to message #341201] Mon, 18 August 2008 21:12 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>How would I convert to hours?

What are the units of measure used by Oracle?

By using a straight forward mathmatical unit conversion.
Re: Date Calculation with Oracle [message #341458 is a reply to message #341450] Mon, 18 August 2008 21:54 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Oops... I got it.Smile
Re: Date Calculation with Oracle [message #341460 is a reply to message #341201] Mon, 18 August 2008 22:00 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
The Posting Guidelines clearly states "If you found an answer yourself, post it."
Previous Topic: no rows
Next Topic: can i open a file through plsql
Goto Forum:
  


Current Time: Thu Dec 08 22:32:24 CST 2016

Total time taken to generate the page: 0.05651 seconds