How to update a TIMESTAMP column to TIMESTAMP WITH TIME ZONE in Oracle

10,048

Solution 1

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

Solution 2

For me it looks good.

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

gives:

2012-06-15 09:46:20

You simply live in country with 4 hour difference to UTC.

Also try something like:

SELECT to_char(new_column_a, 'YYYY-MM-DD HH24:MI:SS TZD'), sys_extract_utc(new_column_a) FROM oopsie_table;
Share:
10,048
Hank Gay
Author by

Hank Gay

I like to spend time with my lovely wife, our two beautiful daughters, and our dog. I'm also a fan of geek humor, and I've been known to flip out and write code… elegant code, if I'm really lucky.

Updated on June 20, 2022

Comments

  • Hank Gay
    Hank Gay almost 2 years

    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;
    

    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).

    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).

  • Hank Gay
    Hank Gay almost 12 years
    Yes, I do live in a timezone that is currently a 4-hour offset, but I have Oracle configured to default to UTC (or at least I tried), which is why I mentioned the output of select dbtimezone from dual;. I will experiment with TO_CHAR to see if I can use that to change the format of the value stored in the new column.