Home » SQL & PL/SQL » SQL & PL/SQL » time difference between two date
time difference between two date [message #194303] Thu, 21 September 2006 07:49 Go to next message
Messages: 26
Registered: August 2005
Location: Chennai
Junior Member


How to find out time difference between two dates. Is there is any build in for this.

Re: time difference between two date [message #194309 is a reply to message #194303] Thu, 21 September 2006 08:02 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In the words of Tom Kyte, It depends.

You can find out the time difference between two dates by subtracting one from the other.

Formatting this is a little trickier.

You can do:
SELECT to_char(trunc(sysdate) + (date_1 - date_2),'hh24:mi:ss') from dual;

or expand it manually by multiplying by 24 to get the Hours, then multiplying the decimal remainder by 60 to get the minutes, and multiplying the decimal remainder of that by 60 to get the seconds.
icon2.gif  Re: time difference between two date [message #194482 is a reply to message #194303] Fri, 22 September 2006 06:20 Go to previous messageGo to next message
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
since i feel that he must be newbie..
you will be able to see the time difference only if it has been entered in the table along with the data.else it will show the difference between 12:00 am to 12:00 AM,since i faced this problem when i was new..thought i should post this.
Re: time difference between two date [message #198021 is a reply to message #194303] Fri, 13 October 2006 19:17 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member

What the two prior poster have said is this:

select date1 - date2 ...

gives the number of days between the two dates. A difference of 1 days thus = 1 and difference of half a day thus equal .5 etc. From here you can use the fact that there are 24 hours in a day, 60 minutes in an hour and 60 seconds in a minute to figure whatever level of day granularity you want. Just do some math.

Additionally, the second poster is pointing out that a date is only as good as what you put it. If you want a date to have a time component you must enter a date with a time component. It sounds obvious but many tools don't do it. Oracle Forms for example has in the past had this issue, of trucating the portion of day from the date. If you are not seeing any difference when subtracting two dates that you thought should give a difference, it may be their time components were truncated when the dates were entered into your system. This was a problem for many tools because people assumed that all dates entered via a tool would contain a time portion when in fact the tool they were using (whatever it was) didn't.

Good luck, Kevin
Previous Topic: Integrity Constraint error
Next Topic: PRAGMA
Goto Forum:

Current Time: Thu Jul 20 14:18:30 CDT 2017

Total time taken to generate the page: 0.10882 seconds