Geek asks and answers

How to update TIMESTAMP column to TIMESTAMP with timezone in Oracle

I have a couple of columns that, unfortunately, were incorrectly defined as TIMESTAMP(6)instead TIMESTAMP(6) WITH TIME ZONE. I would like to transfer these columns from the old, incorrect data type to the new, correct one. Also, the values ​​were apparently fixed in E (S | D) T, and I need the value in UTC.

So far I have had the best:

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;

Unfortunately, this leaves me with data that looks like 15-JUN-12 05.46.29.600102000 PM -04:00when I want 15-JUN-12 09.46.29.600102000 PM UTC(or, be that as it may, Oracle formatted it).

select dbtimezone from dual;, +00:00, , . DML DST (, , America/New_York).

+5
2

@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'
;
+3

.

`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;
+2

More articles:


All Articles