Oracle 如何在不损失分数的情况下将年份添加到时间戳中

嗨，

我有一列类型

TIMESTAMP
复制

t其中I安全的时间戳与6位精度，例如03-FEB-17 04.45.01.909000

由于他们将ben添加到表格中的方式，他们缺少两件事:
1.时区信息
2.正确的世纪

关于1: 要添加时区，我创建了一个新的列t_tz类型

TIMESTAMP(6) WITH TIME ZONE
复制

并希望将旧的复制到新列中，并使用以下方法添加时区:

UPDATE myTable
  SET t_tz = t
复制

关于2: 显示03-FEB-17很烦人，因为数据库将该值理解为2月3日17 BC，但应该2017年2月3日。我试过了

add_month( t, 2000 * 12 )
复制

为了获得正确的年份，但在此过程中会丢失小数秒，例如
03-FEB-17 04.45.01.909000-> 03-FEB-17 04.45.01.000000-05:00

实现这两个目标的正确方法是什么。

尽管进行了广泛的搜索，但我还是找不到匹配的答案。

1.是的，您可以将新的时间戳设置为时区列等于原始时间:

alter session set nls_timestamp_format = 'DD-MON-YYYY HH24.MI.SSXFF';
alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24.MI.SSXFF TZH:TZM';

create table t (
  ts timestamp
);

insert into t values (timestamp'0017-02-03 04:45:01.909');

select * from t;

alter table t add (tsz timestamp with time zone);

update t 
set    tsz = ts;

select * from t;

TS                              
03-FEB-0017 04.45.01.909000000 

update t 
set    tsz = to_timestamp_tz (
         to_char(ts, 'YYYY-MM-DD HH:MI:SSXFF') || ' -08:00', 
         'YYYY-MM-DD HH:MI:SSXFF TZH:TZM'
       );

TS                              TSZ                                    
03-FEB-0017 04.45.01.909000000  03-FEB-0017 04.46.01.909000000 +00:00
复制

虽然它是更好的实践，明确声明你的转换。这也使您可以设置特定的时区，如果你想要:

update t 
set    tsz = to_timestamp_tz (
         to_char(ts, 'YYYY-MM-DD HH:MI:SSXFF') || ' -08:00', 
         'YYYY-MM-DD HH:MI:SSXFF TZH:TZM'
       );

select * from t;

TS                              TSZ                                    
03-FEB-0017 04.45.01.909000000  03-FEB-0017 04.45.01.909000000 -08:00
复制

如果您的表是 “大”，则使用 “创建为选择” 创建具有正确数据类型的新表可能会更好 (更快):

create table t_new as
  select to_timestamp_tz (
           to_char(ts, 'YYYY-MM-DD HH:MI:SSXFF') || ' -08:00', 
           'YYYY-MM-DD HH:MI:SSXFF TZH:TZM'
         ) ts
  from   t;
复制

然后有一个过程将约束复制到新表并将其切换过来。

2. Add_months隐式将时间戳转换为日期这就是为什么你会失去小数秒:

alter session set nls_timestamp_format = 'DD-MON-YYYY HH24.MI.SSXFF';

select add_months (
         to_timestamp (
           '03-FEB-0017 04.45.01.909000 PM', 
           'DD-MON-YYYY HH:MI:SS.FF AM'
         ), 
         2000*12
       ) t         
from   dual;

T                     
03-FEB-2017 16:45:01 
复制

为了克服这个问题，增加一个2000年年的间隔，避免隐式转换:

select to_timestamp (
           '03-FEB-0017 04.45.01.909000 PM', 
           'DD-MON-YYYY HH:MI:SS.FF AM'
       ) + interval '2000' year(4) t         
from   dual;

T                               
03-FEB-2017 16.45.01.909000000 
复制