Converting INTERVAL to single number
535381Sep 22 2006 — edited Sep 22 2006I 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?