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.