Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: TIMESTAMP( ) in pl/sql

    So you can designate a TIMESTAMP with a parm for...

    "where the optional parameter precision specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The default is 6."

    But, if you try something like

    Code:
    declare
      l_tm timestamp(4) := current_timestamp;
    begin
      dbms_output.put_line(to_char(l_tm,'FF'));
    end;
    /
    222545000
    You'll get all 9 digits. It's like it ignores the precision.

    But if you try & compare 2 timestamps with different precision, then you get what you'd expect. Makes me not want to trust the precision:

    Code:
    set define off;
    declare
      l_curr  timestamp(9) := current_timestamp;
      l_tm1   timestamp(4);
      l_tm2   timestamp(9);
    begin
      null;
      l_tm1 := l_curr;
      l_tm2 := l_curr;
      
      if l_curr = l_tm1 then
        dbms_output.put_line('l_tm1 is a match!');
      end if;
      
      if l_curr = l_tm2 then
        dbms_output.put_line('l_tm2 is a match!');
      end if;
      
      if l_tm2 != l_tm1 then
        dbms_output.put_line('l_tm1 & 2 are not a match!');
      else
        dbms_output.put_line('l_tm1 & 2 are a match!');
      end if;
    
      l_tm2 := l_tm1;
      
      if l_tm2 != l_tm1 then
        dbms_output.put_line('l_tm1 & 2 are not a match!');
      else
        dbms_output.put_line('l_tm1 & 2 are a match!');
      end if;
      
    end;
    /
    
    l_tm2 is a match!
    l_tm1 & 2 are not a match!
    l_tm1 & 2 are a match!

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Well if you do a to_char(current_timestamp, 'SSXFF') you'll most likely find that your precision is only to microseconds (timestamp(5)). The remaining decimal places will be 0 and your ts(9) will actually match your ts(4) one out of ten times.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I don't think that's the case. If you compare a TIMESTAMP(4) to a TIMESTAMP(5), and the fifth character of the microseconds is a "0", they still don't end up being equal

    Code:
    set define off;
    declare
      l_curr  timestamp(5);
      l_tm1   timestamp(4);
      l_cnt   number(4);
    begin
      l_curr := current_timestamp;
      l_cnt := 0;
      
      while substr(to_char(l_curr, 'FF5'),5,1) != 0
      loop 
        l_curr := current_timestamp;
        l_cnt := l_cnt + 1;
        if l_cnt > 9998 then
          exit;
        end if;
      end loop;
      
      
      if l_curr = l_tm1 then
        dbms_output.put_line('l_tm1 is a match! '||to_char(l_curr, 'YYYY/MM/DD HH24:MI:SS.FF5'));
      else
        dbms_output.put_line('l_tm1 is not a match! '||to_char(l_curr, 'YYYY/MM/DD HH24:MI:SS.FF5'));
      end if;
      
      
    end;
    /
    
    l_tm1 is not a match! 2009/09/24 15:47:14.37190

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •