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

问题描述

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

I have a pair of columns that were unfortunately defined incorrectly as TIMESTAMP(6) instead of TIMESTAMP(6) WITH TIME ZONE. I would like to migrate those columns from the old, wrong datatype to the new, correct one. On top of that, the values appear to have been captured in E(S|D)T and I need the value in UTC.

到目前为止，我得到的最好的是:

So far, the best I've got is:

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 09.46.29.600102000 PM UTC时，这给了我类似15-JUN-12 05.46.29.600102000 PM -04:00的数据(或者Oracle对其进行了格式化).

Unfortunately, that leaves me with data that looks like 15-JUN-12 05.46.29.600102000 PM -04:00, when I want 15-JUN-12 09.46.29.600102000 PM UTC (or however Oracle would format it).

我已经完成了select dbtimezone from dual;，并且显示了+00:00，所以我不确定如何继续.理想情况下，我将能够在纯DML中执行此操作，并根据旧的日期值(我确定该时间在America/New_York时区中)将其用于DST.

I've done select dbtimezone from dual; and it shows me +00:00, so I'm not sure how to proceed. Ideally, I would be able to do this in pure DML, and have it account for DST based on the old date values (which I'm sure are in the America/New_York timezone).

推荐答案

在 @JustinCave的帮助下，我得出以下解决方案，完全满足了我的要求:

With a little help from @JustinCave, I arrived at the following solution, which accomplishes exactly what I wanted:

-- 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'
;

这篇关于如何在Oracle中将TIMESTAMP列更新为TIMESTAMP WITH TIME ZONE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！