I'm delving into timestamps for the first time, having years of comfortable familiarity with the pitfalls of existing DATE handling in Oracle. I am currently trying to write a simple procedure to take two timestamp inputs, including seconds to 4 fractional places, and output the total elapsed time in human form (days/hours/minutes/seconds) and seconds.

Here's the code:

CREATE OR REPLACE PROCEDURE datediff(
      p_date1 IN VARCHAR2,
      p_date2 IN VARCHAR2 )
IS
   l_date1 VARCHAR2(60);
   l_date2 VARCHAR2(60);

   l_ts1   TIMESTAMP;
   l_ts2   TIMESTAMP;

   l_int   INTERVAL DAY(6) TO SECOND(4);
   l_diff  NUMBER;

   PROCEDURE p( p_text IN VARCHAR2 )
   IS
   BEGIN
      dbms_output.put_line( p_text );
   END p;
BEGIN

   l_date1 := TRIM(p_date1);
   l_date2 := TRIM(p_date2);

   IF INSTR( p_date1, ' ' ) = 0 
      OR INSTR( p_date2, ' ' ) = 0 THEN
      p( 'Malformed input.');
      RETURN;
   END IF;

   l_ts1 := TO_TIMESTAMP( p_date1, 'MM/DD/YYYY HH24:MI:SS.FF4' );
   l_ts2 := TO_TIMESTAMP( p_date2, 'MM/DD/YYYY HH24:MI:SS.FF4' );

   IF l_ts1 > l_ts2 THEN
      l_int := l_ts1 - l_ts2;

      p( 'Start time: ' || 
         TO_CHAR( EXTRACT( MONTH FROM l_ts2 ), 'fm00' ) || '/' ||
         TO_CHAR( EXTRACT( DAY FROM l_ts2 ),'fm00' ) || '/' ||
         TO_CHAR( EXTRACT( YEAR FROM l_ts2 ), 'fm0000' ) || ' ' ||
         TO_CHAR( EXTRACT( HOUR FROM l_ts2 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( MINUTE FROM l_ts2 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( SECOND FROM l_ts2 ), 'fm00.0' ) );
      p( 'End time:   ' || 
         TO_CHAR( EXTRACT( MONTH FROM l_ts1 ), 'fm00' ) || '/' ||
         TO_CHAR( EXTRACT( DAY FROM l_ts1 ),'fm0000' ) || '/' ||
         TO_CHAR( EXTRACT( YEAR FROM l_ts1 ), 'fm00' ) || ' ' ||
         TO_CHAR( EXTRACT( HOUR FROM l_ts1 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( MINUTE FROM l_ts1 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( SECOND FROM l_ts1 ), 'fm00.0' ) );
   ELSE
      l_int := l_ts2 - l_ts1;
      p( 'Start time: ' || 
         TO_CHAR( EXTRACT( MONTH FROM l_ts1 ), 'fm00' ) || '/' ||
         TO_CHAR( EXTRACT( DAY FROM l_ts1 ),'fm00' ) || '/' ||
         TO_CHAR( EXTRACT( YEAR FROM l_ts1 ), 'fm0000' ) || ' ' ||
         TO_CHAR( EXTRACT( HOUR FROM l_ts1 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( MINUTE FROM l_ts1 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( SECOND FROM l_ts1 ), 'fm00.0' ) );
      p( 'End time:   ' || 
         TO_CHAR( EXTRACT( MONTH FROM l_ts2 ), 'fm00' ) || '/' ||
         TO_CHAR( EXTRACT( DAY FROM l_ts2 ),'fm00' ) || '/' ||
         TO_CHAR( EXTRACT( YEAR FROM l_ts2 ), 'fm0000' ) || ' ' ||
         TO_CHAR( EXTRACT( HOUR FROM l_ts2 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( MINUTE FROM l_ts2 ), 'fm00' ) || ':' ||
         TO_CHAR( EXTRACT( SECOND FROM l_ts2 ), 'fm00.0' ) );
   END IF;

   p( 'Difference: ' || TO_CHAR( EXTRACT( DAY FROM l_int ) ) || ' days ' ||
      TO_CHAR( EXTRACT( HOUR FROM l_int ) ) || ' hrs ' ||
      TO_CHAR( EXTRACT( MINUTE FROM l_int ) ) || ' mins ' ||
      TO_CHAR( EXTRACT( SECOND FROM l_int ), '90.0' ) || ' secs' );

   l_diff := ( EXTRACT( DAY FROM l_int ) * 24 * 60 * 60 ) +
             ( EXTRACT( HOUR FROM l_int ) * 60 * 60 ) +
             ( EXTRACT( MINUTE FROM l_int ) * 60 ) +
             ( EXTRACT( SECOND FROM l_int ) );
   p( 'Which is ' || TO_CHAR(l_diff, '999999990.0') || ' seconds');

END datediff;
/
show errors

But in the output below, notice how the fractional seconds are being truncated in the internal timestamp variables, which is affecting the final seconds calculation.

SQL> set serveroutput on size 1000000
SQL> exec datediff('01\04\2011 17:02:39.0059', '01\04\2011 17:03:02.0201');
Start time: 01/04/2011 17:02:39.0
End time:   01/04/2011 17:03:02.0
Difference: 0 days 0 hrs 0 mins  23.0 secs
Which is         23.0 seconds

What am I doing wrong?

(P.S. The 10.2.0.4 is just one of the databases I'm using. Have plenty of 11g around.)

ops$tkyte%ORA11GR2> exec datediff('01\04\2011 17:02:39.0059', '01\04\2011 17:03:02.0201');
Start time: 01/04/2011 17:02:39.0059
End time:   01/04/2011 17:03:02.0201
Difference: 0 days 0 hrs 0 mins  23.0142 secs
Which is         23.0142 seconds

PL/SQL procedure successfully completed.