sql - 为什么 Oracle 从小数秒中去除前导 0？

Question

我正在验证一些批量输入(即纯文本)；验证的第一阶段是确保我必须订购的列实际上是正确的 timestamp在尝试将其放入我的时间戳之前格式化。在这种情况下 'yyyy/mm/dd hh24:mi:ss:ff2' .

但是，似乎 Oracle 从时间戳格式的小数秒精度中删除了前导 0。例如 009假定精度为 2(或更小)，如 0099 ，但不是 0090 .前两个例子显然是不正确的。就日期时间格式模型而言，小数秒精度似乎是精度，不包括前导 0。

无论精度如何，这种行为似乎都会发生。

这个例子是正确的:

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

这些例子是不正确的:

我预计会出现错误，但可以处理它被截断的情况。

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

这个错误是正确的；它的小数秒精度为 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

我正在使用 11.2.0.1.0 版，但这种行为也出现在 11.1.0.6.0 和 9.2.0.1.0 中，所以它显然已经存在了一段时间。

这是我以前不知道的“功能”吗？

该解决方案似乎假设所有时间戳的精度为 6 位，但是否还有另一个可以实际验证我提供的数据是否正确？

Answer 1

我相信你已经有了一个计划，但我想我会玩。 to_char似乎用 .ff2 截断为两位数- 我失去了它的逻辑 - 所以如果你很高兴提供的值被截断，你可以通过它反弹(以一种丑陋的，不应该是必要的方式):

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

或者你当然可以把它放在一个函数中:

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

或者您可以使用相同的机制使其出错:

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

您可以添加一个异常和编译指示，使其改为抛出 ORA-01880，但我不确定该消息是否完全有用。