CODEWITHSUNDEEP
sqloracle-databasetimestamp
Ben
Ben

Reputation: 52893

Why does Oracle strip leading 0s from fractional seconds?

I'm validating some batch input (i.e. plain text); the first stage of the validation is to ensure that the column I have to order by is actually in the correct timestamp format before trying to place it into my timestamp. In this case 'yyyy/mm/dd hh24:mi:ss:ff2'.

However, it appears as though Oracle removes leading 0s from the fractional seconds precision of a timestamp format. For instance 009 is assumed to be a precision of 2 (or less), as is 0099, but not 0090. The first two examples are, obviously, incorrect. It appears as though, for the purposes of a date-time format model, the fractional seconds precision is the precision, excluding leading 0s.

The behaviour seems to occur whatever the precision.

This example is correct:

select to_timestamp('2012/06/20 05:12:41:91','yyyy/mm/dd hh24:mi:ss:ff2') t
  from dual;

T
---------------------------------------------------------------------------
20-JUN-12 05.12.41.910000000

These examples are incorrect:

I would expect an error but could deal with it being truncated.

select to_timestamp('2012/06/20 05:12:41:091','yyyy/mm/dd hh24:mi:ss:ff2') t
  from dual;

 T
---------------------------------------------------------------------------
20-JUN-12 05.12.41.091000000

select to_timestamp('2012/06/20 05:12:41:0091','yyyy/mm/dd hh24:mi:ss:ff2') t
  from dual;

T
---------------------------------------------------------------------------
20-JUN-12 05.12.41.009100000

select to_timestamp('2012/06/20 05:12:41:00091','yyyy/mm/dd hh24:mi:ss:ff2') t
  from dual;

T
---------------------------------------------------------------------------
20-JUN-12 05.12.41.000910000

This error is correct; it has a fractional seconds precision of 3.

select to_timestamp('2012/06/20 05:12:41:901','yyyy/mm/dd hh24:mi:ss:ff2') t
  from dual;
select to_timestamp('2012/06/20 05:12:41:901','yyyy/mm/dd hh24:mi:ss:ff2') t
                    *
ERROR at line 1:
ORA-01880: the fractional seconds must be between 0 and 999999999

I'm using release 11.2.0.1.0 but this behaviour also appears in 11.1.0.6.0 and 9.2.0.1.0 so it's obviously been around for a while.

Is this a "feature" I was previously unaware of?

The solution seems to be assuming that all the timestamps have a precision of 6 digits but is there another that can actually validate the the data I've been given is correct?

Upvotes: 4

Views: 2622

Answers (1)

Alex Poole
Alex Poole

Reputation: 191415

I'm sure you have a plan already, but thought I'd have a play. to_char seems to truncate to two digits with .ff2 - the logic of that is lost on me - so if you're happy to have the supplied value truncated you can bounce it through that (in an ugly, shouldn't-be-necessary kind of way):

select to_timestamp(
        to_char(
            to_timestamp('2012/06/20 05:12:41:091',
                'yyyy/mm/dd hh24:mi:ss:ff9'),
            'yyyy/mm/dd hh24:mi:ss:ff2'),
        'yyyy/mm/dd hh24:mi:ss:ff2') t
from dual;

T
---------------------------------------------------------------------------
20-JUN-12 05.12.41.090000000

Or you can put that in a function of course:

create or replace function my_to_timestamp(p_str varchar2)
return timestamp is
begin
    return to_timestamp(
            to_char(
                to_timestamp(p_str, 'yyyy/mm/dd hh24:mi:ss:ff9'),
                'yyyy/mm/dd hh24:mi:ss:ff2'),
            'yyyy/mm/dd hh24:mi:ss:ff2');
end;
/

select my_to_timestamp('2012/06/20 05:12:41:91') from dual;

MY_TO_TIMESTAMP('2012/06/2005:12:41:91')
---------------------------------------------------------------------------
20-JUN-12 05.12.41.910000000

select my_to_timestamp('2012/06/20 05:12:41:091') from dual;

MY_TO_TIMESTAMP('2012/06/2005:12:41:091')
---------------------------------------------------------------------------
20-JUN-12 05.12.41.090000000

select my_to_timestamp('2012/06/20 05:12:41:901') from dual;

MY_TO_TIMESTAMP('2012/06/2005:12:41:901')
---------------------------------------------------------------------------
20-JUN-12 05.12.41.900000000

Or you can make it error using the same mechanism:

create or replace function my_to_timestamp(p_str varchar2)
return timestamp is
    ts timestamp;
begin
    ts := to_timestamp(p_str, 'yyyy/mm/dd hh24:mi:ss:ff9');
    if ts != to_timestamp(
                to_char(
                    to_timestamp(p_str, 'yyyy/mm/dd hh24:mi:ss:ff9'),
                    'yyyy/mm/dd hh24:mi:ss:ff2'),
                'yyyy/mm/dd hh24:mi:ss:ff2')
    then
        raise program_error;
    end if;
    return ts;
end;
/

select my_to_timestamp('2012/06/20 05:12:41:91') from dual;

MY_TO_TIMESTAMP('2012/06/2005:12:41:91')
---------------------------------------------------------------------------
20-JUN-12 05.12.41.910000000

select my_to_timestamp('2012/06/20 05:12:41:091') from dual;

select my_to_timestamp('2012/06/20 05:12:41:091') from dual
       *
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "SCOTT.MY_TO_TIMESTAMP", line 12

select my_to_timestamp('2012/06/20 05:12:41:901') from dual;

select my_to_timestamp('2012/06/20 05:12:41:901') from dual
       *
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "SCOTT.MY_TO_TIMESTAMP", line 12

You could add an exception and pragma to make it throw ORA-01880 instead, but I'm not sure that message is entirely useful anyway.

Upvotes: 1

Related Questions