我正在验证一些批处理输入（即纯文本）；验证的第一步是确保在尝试将其放入时间戳之前，我必须排序的列实际上采用正确的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位，但是还有另一个可以实际验证我得到的数据是否正确吗？
最佳答案
我确定您已经有一个计划，但是认为我会有所作为。 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，但是我不确定该消息是否完全有用。