Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Converting INTERVAL to single number

535381Sep 22 2006 — edited Sep 22 2006
I have two TIMESTAMP values. The difference, obviously, is INTERVAL DAY TO SECOND. I want to know the total time in the interval measured in seconds. I know that I can do 4 EXTRACT functions to get days, hours, minutes, and seconds separately; followed by 3 multiplications and 3 adds. I hope I'm not the only one who thinks this is overly complicated. The old-fashioned DATE data type would give me exactly what I wanted with a single subtraction and multiplication. But the resolution of DATE is only 1 second and I need the fractional resolution that TIMESTAMP will give me. If the complicated method is the only one available, then I'm seriously disappointed in the functionality of TIMESTAMP and INTERVAL.

All of my attempts to convert INTERVAL to any numeric type have failed.

Does anyone know of a quick way to get the total seconds from an INTERVAL without extracting the 4 fields separately?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 20 2006
Added on Sep 22 2006
2 comments
2,591 views