Date Calculation with Oracle [message #341201] |
Sun, 17 August 2008 20:19  |
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 #341205 is a reply to message #341202] |
Sun, 17 August 2008 20:54   |
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 #341224 is a reply to message #341213] |
Sun, 17 August 2008 21:44   |
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
|
|
|
|
|
|
|
|
|
|
|
|