Why does Oracle separate leading 0s from fractional seconds?

Question

I check some batch input (i.e. plain text); The first step in checking is to make sure that the column I must order is in the correct timestamp format before trying to put it in my timestamp. In this case, 'yyyy/mm/dd hh24:mi:ss:ff2' .

However, it looks like Oracle is removing leading 0s from the precision of fractional seconds of the timestamp format. For example, 009 is considered an accuracy of 2 (or less), like 0099 , but not 0090 . The first two examples are obviously incorrect. It seems that for a date and time format model, the accuracy of fractional seconds is accuracy, excluding the initial 0.

The behavior seems to occur regardless of accuracy.

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 a mistake, but could deal with its truncation.

 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 second accuracy 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 am using release 11.2.0.1.0, but this behavior also appears in 11.1.0.6.0 and 9.2.0.1.0, so it has obviously been around for a while.

Is this a “feature” that I did not know about before?

The solution seems to suggest that all timestamps are 6 digits accurate, but is there any other that can really validate the data I gave you?

Answer 1

I am sure that you already have a plan, but I thought that I would have a game. to_char seems to truncate to two digits with .ff2 - the logic of this gets lost on me, so if you are happy that the truncated set value you can bounce off of it (in the ugly, be necessary 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 this 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 a mistake 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 can add an exception and pragma to make it throw an ORA-01880 instead, but I'm not sure if the message is useful at all.