Difference between dates in seconds (9i)

9i introduces the interval datatype and this is now the type returned from a date subtraction. The following code will display the difference between the dates:

declare
duration interval day(6) to second(6);
v_start timestamp := to_timestamp('5-NOV-2003 14:00:00.000000', 'DD-MON-YYYY HH24:MI:SSxFF');
v_end timestamp(6) := sysTimestamp;
begin
duration := v_end - v_start;
dbms_output.put_line ('Now: '||to_char(v_end,'DD-MON-YYYY HH24:MI:SSxFF'));
dbms_output.put_line ('Difference: '|| to_char(duration)); end;

BUT, the result is:
Now: 06-NOV-2003 12:24:22.244127000
Difference: +000000 22:24:22.244127

PL/SQL procedure successfully completed.

I don't want to display the value! I need to extract the hours,minutes and seconds to save the number of seconds.

Any ideas???

Michael Gill
Sr. Database Administrator
mydba_at_usa.com Received on Fri Nov 07 2003 - 13:47:31 CST