Как обновить столбец TIMESTAMP до TIMESTAMP WITH TIME ZONE в Oracle

У меня есть пара столбцов, которые, к сожалению, были неправильно определены как 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 их отформатировал).

Я сделал select dbtimezone from dual;, и он показывает мне +00:00, поэтому я не знаю, что делать дальше. В идеале я мог бы сделать это в чистом DML и учесть летнее время на основе старых значений даты (которые, я уверен, находятся в часовом поясе America/New_York).


oracle data-migration timestamp timezone ddl
person Hank Gay    schedule 18.06.2012    source источник

Ответы (2)


arrow_upward
6
arrow_downward

С небольшой помощью @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'
;
person Hank Gay    schedule 19.06.2012

arrow_upward
2
arrow_downward

Для меня это выглядит хорошо.

`SELECT SYS_EXTRACT_UTC(TIMESTAMP '2012-06-15 05:46:20 -04:00') FROM DUAL;`

дает:

2012-06-15 09:46:20

Вы просто живете в стране с разницей в 4 часа с UTC.

Также попробуйте что-то вроде:

SELECT to_char(new_column_a, 'YYYY-MM-DD HH24:MI:SS TZD'), sys_extract_utc(new_column_a) FROM oopsie_table;
person Michał Niklas    schedule 19.06.2012
comment
Да, я живу в часовом поясе, который в настоящее время смещен на 4 часа, но у меня есть Oracle, настроенный по умолчанию на UTC (или, по крайней мере, я пытался), поэтому я упомянул вывод select dbtimezone from dual;. Я поэкспериментирую с TO_CHAR, чтобы посмотреть, смогу ли я использовать его для изменения формата значения, хранящегося в новом столбце. - person Hank Gay; 19.06.2012