Anyone know how to work out the number of seconds between two dates in Oracle?
i.e. endtime - starttime = duration
09/03/2000 09:01:30 - 09/03/2000 09:00:00 = 90
Regards
Brian Patterson
Listserv Archives (BOB member since 2002-06-25)
In a message dated 00-03-09 06:58:46 EST, you write:
Anyone know how to work out the number of seconds between two dates in
Oracle?
i.e. endtime - starttime = duration
09/03/2000 09:01:30 - 09/03/2000 09:00:00 = 90
If you are talking about doing the calculation in the Oracle database, then it’s easy: simply subtract the two dates and convert from days to seconds. In the case above, it would be:
select (endtime - starttime) * 86400
from table…
Oracle stores dates as date / time values. If you subtract, you get a decimal number of days. For example, today at noon minus yesterday at midnight would return 1.5, as in one and one half days. To convert that to seconds, you must multiply the number of days by the number of hours in a day (24), then by the number of minutes in an hour (60), then by the number of seconds in a minute (60). It turns out that 24 * 60 * 60 = 86400.
If you are trying to do the same subtraction in a BusObj report, then you are in for more work. BusObj has the DaysBetween() function, but it ignores times. You have to strip apart the date / time value using substr(), then convert each portion of the time to a number, subtract and convert accordingly, then finally put everything back together. A sample formula to do this can be found in a presentation, “Variables and More Variables, the Sequel” on our web site on the BusObj Conference page:
http://www.islink.com/bobjconf.htm
I wish they would either fix the DaysBetween() function to recognize times, or give us a TimeBetween() function. Enhancement request?
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)
select (endtime - starttime) * 86400
from table…
Thanks Dave, just what I was looking for - sometime you can easily miss the wood for the trees.
Brian Patterson
Listserv Archives (BOB member since 2002-06-25)