如何在Oracle中将TIMESTAMP列更新为TIMESTAMP WITH TIME ZONE

我有一对列不幸被错误地定义为TIMESTAMP(6)而不是TIMESTAMP(6)WITH TIME ZONE.我想将这些列从旧的错误数据类型迁移到新的,正确的数据类型.最重要的是,值似乎已在E(S | D)T中捕获,我需要UTC中的值.

到目前为止,我得到的最好的是：

alter table OOPSIE_TABLE add (
    NEW_COLUMN_A timestamp(6) with time zone,NEW_COLUMN_B timestamp(6) with time zone
);
update OOPSIE_TABLE set
    NEW_COLUMN_A = COLUMN_A,NEW_COLUMN_B = COLUMN_B
;
alter table OOPSIE_TABLE drop column (
    COLUMN_A,COLUMN_B
);
alter table OOPSIE_TABLE rename column NEW_COLUMN_A to COLUMN_A;
alter table OOPSIE_TABLE rename column NEW_COLUMN_B to COLUMN_B;

不幸的是,这让我的数据看起来像15-JUN-12 05.46.29.600102000 PM -04：00,当我想要15-JUN-12 09.46.29.600102000 PM UTC(或者Oracle会格式化它).

我已经完成了从双重选择dbtimezone;它显示我00:00,所以我不知道如何继续.理想情况下,我可以在纯DML中执行此操作,并根据旧日期值(我确定在America / New_York时区中)将其计入DST.

解决方法

有了
little help from @JustinCave,我得到了以下解决方案,它完全符合我的要求：

-- Rename the old columns so we can use them as a data source *AND* so
-- we can roll back to them if necessary.
alter table OOPSIE_TABLE rename column COLUMN_A to OLD_COLUMN_A;
alter table OOPSIE_TABLE rename column COLUMN_B to OLD_COLUMN_B;
-- Define COLUMN_A and COLUMN_B to have TIME ZONE support.
alter table OOPSIE_TABLE add (
    COLUMN_A timestamp(6) with time zone,COLUMN_B timestamp(6) with time zone
);
-- Populate the "new" columns with the adjusted version of the old data.
update OOPSIE_TABLE set
    COLUMN_A = from_tz(OLD_COLUMN_A,'America/New_York') at time zone 'UTC',COLUMN_B = from_tz(OLD_COLUMN_B,'America/New_York') at time zone 'UTC'
;