Home » SQL & PL/SQL » SQL & PL/SQL » Time difference (Oracle 9i)
Time difference [message #442782] Wed, 10 February 2010 08:45 Go to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
Hi All,
Please help me on the folloewing scenario.

I want to find the Time difference for the following data

DD-MON-YYYY HH MI 24 Format
01-Jan-2010 22:00
03-Jan-2010 04:00

the output should be 6 Hours,how can we do that please help me.
Thanks in advance.
Re: Time difference [message #442783 is a reply to message #442782] Wed, 10 February 2010 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can directly make the difference bewteen two dates, the result is in unit of days.
There are 24 hours per day, 60 minutes per hour and 60 seconds per minute.

Regards
Michel
Re: Time difference [message #442785 is a reply to message #442782] Wed, 10 February 2010 08:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd do something like this:
with src as (select to_date('03-Jan-2010 04:00','dd-mon-yyyy hh24_mi') - to_date('01-Jan-2010 22:00','dd-mon-yyyy hh24:mi') col_1 
             from dual)
select (col_1 - trunc(col_1))*24 hours  from src;
Re: Time difference [message #442799 is a reply to message #442782] Wed, 10 February 2010 12:22 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
If i changed the date it's showing different result.
i need a answer it should work to find difference between any dates.In this scenario i want to find the difference between

22:00 and 04:00 no need consider dates.

Please help me

Re: Time difference [message #442800 is a reply to message #442799] Wed, 10 February 2010 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i want to find the difference between
22:00 and 04:00 no need consider dates.

And what is this difference? For me it is -18 hours.

Regards
Michel
Re: Time difference [message #442803 is a reply to message #442782] Wed, 10 February 2010 12:58 Go to previous messageGo to next message
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
Senior Member
It should come 6 hours in the out put.
And also one more question why the following query showing

-0.583 as result

SELECT to_date('01-Jan-2010 08:00', 'dd-mon-yyyy hh24_mi') -
to_date('01-Jan-2010 22:00', 'dd-mon-yyyy hh24:mi') col_1
FROM dual

please help me.
Re: Time difference [message #442804 is a reply to message #442803] Wed, 10 February 2010 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It should come 6 hours in the out put.

Why?

Quote:
And also one more question why the following query showing -0.583 as result

Please read the answers we give you:
Quote:
You can directly make the difference bewteen two dates, the result is in unit of days.
There are 24 hours per day, 60 minutes per hour and 60 seconds per minute.

Quote:
select (col_1 - trunc(col_1))*24 hours from src;


Regards
Michel

[Updated on: Wed, 10 February 2010 13:03]

Report message to a moderator

Re: Time difference [message #442812 is a reply to message #442804] Wed, 10 February 2010 13:47 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
22:00 and 04:00 no need consider dates.


As if the Y2K bug was not enough, not people are working on a bugs that might strike every day at midnight. Or a few years from now when somebody decides to run an overnight batch at 01:00 instead of 22:00, and nobody remembers this buggy code, or the person who wrote it.

Re: Time difference [message #442888 is a reply to message #442799] Thu, 11 February 2010 03:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
If i changed the date it's showing different result.
i need a answer it should work to find difference between any dates.In this scenario i want to find the difference between

22:00 and 04:00 no need consider dates.


When I run it, as long as the date with a time of 04:00 is on a later day than the time,it works.
If you felt like providing any information at all, then I could be more help.

Why don't you try describing the problem that you're trying to solve, rather than get us to fix the slightly ropey solution you've chosen.
Previous Topic: Forward dependencies of a Database object
Next Topic: Basic PL/SQL
Goto Forum:
  


Current Time: Wed Dec 07 04:39:14 CST 2016

Total time taken to generate the page: 0.14782 seconds