Want to show your appreciation? Please to my charity.

Saturday, April 25, 2009

Converting Oracle Interval Data Type to Seconds

I have been searching for a solution to convert oracle Interval data type to number type of seconds. To my double surprise: a) Although I know Oracle is database that full of holes and patches, but still surprised that it didn't provide such a basic function; b) this time Google failed to find a solution for such a common request. Tom actually had to write up a sum function for interval. The closest that fit my need is this one, which uses below technique to get seconds.

(TRUNC(SYSDATE) + the_interval - TRUNC(SYSDATE)) * 86400 AS seconds

But that has two major problems:

  • It is rare but can happen that the first sysdate returns day1 23:59 and second sysdate returns day2 00:00. Of course this gives you completely wrong result. See update below.
  • It loses the fraction of seconds.

After given a few tries, I settled on below expression:

(SYSDATE + the_interval*86400 - SYSDATE) AS seconds

This returns accurate result with factions preserved. There are chance that the 2nd sysdate is one second more to the first sysdate but that only change result by 1/86400 second which I don't really care at this moment.

The sysdate can actually be replaced with any date column, variable or to_date('1-jan-1900') to eliminate the 1/86400 second error.

Update (4/28/09): Shammat made a very good point that the SYSDATE provide read consistency inside a single SQL statement. Thus my comment about "off by one day" was wrong. But the read consistency is not there in PL/SQL, thus please make sure assign SYSDATE to a variable and then use that variable instead.

10 comments:

wigbam said...

While this method is good for short intervals, long intervals will raise
"(full) year must be between -4713 and +9999, and not be 0" error.

Kenneth Xu said...

wigbam, you are right on this. Depends on what kind of precision you want, if you don't care about the fraction, the

(SYSDATE + the_interval - SYSDATE) *86400 AS seconds

Will work better for long intervals. And you can always adjust between them to get your desired size and precision. For example:

(SYSDATE + the_interval*100 - SYSDATE) *864 AS seconds

im_chc said...

Are there any reason to use sysdate in particular?
is it for performance reason?
How is it different from using To_Date('1-1-0001', 'DD-MM-YYYY')?

Kenneth Xu said...

Good question, I didn't test. For a long term solution, you should write a function to get different portion of interval and add them together. This solution is rather for ad hoc queries when you don't have the function handy. So there probably won't be much difference between to_date and sysdate but sysdate is easier to type. :)

Anonymous said...

You don't need to involve SYSDATE.
Just add zero and it will do the trick:

(the_interval + 0) * 86400 seconds

Julien Rebreyend said...

Hi,

I spent some time looking for a solution to this problem, and your method seems to work. But I found a much simpler Oracle function, EXTRACT, that does the job.
Just use :
EXTRACT (SECOND FROM the_interval)

http://www.techonthenet.com/oracle/functions/extract.php

I found this solution reading this post :
http://forums.oracle.com/forums/thread.jspa?threadID=850940
Seems that when substracting two timestamps, you get an "interval to second" type and have to use the extract function ; and that this other solution is to cast your timestamps to dates so that substracting gives directly a number.

Kenneth Xu said...

Julien, The extract function doesn't give you the total seconds, it only give you the second portion of the interval. For example, if the interval is 1 minute and 5 seconds, the extract function returns you 5 instead 65, be careful.

Dave Benham said...

The technique is handy, but it does not quite work as advertized - it does not preserve the fractional seconds:

SQL> select (sysdate + NUMTODSINTERVAL(111045.93,'SECOND') - sysdate) * 86400 as sec from dual;

SEC
----------
111045

Kenneth Xu said...

Dave, if you re-read the blog post again, it was telling you to *avoid* using the way you just posted.

Give it a 2nd read and try:

select (sysdate + NUMTODSINTERVAL(111045.93,'SECOND') * 86400 - sysdate) as sec from dual;

HTH

Anonymous said...

Thanks for the useful technique!
There is a discussion on this here:
http://stackoverflow.com/questions/450581/how-to-average-time-intervals/

Post a Comment