I have some data that is stored in a TIMESTAMP(6) WITH TIMEZONE
column in Oracle, but it is stored in the wrong timezone. By convention, all timestamps in the DB must be stored in UTC, but this data was incorrectly persisted as EDT. The actual values are equivalent to the correct UTC value; the problem is simply that it is stored as 19-JUN-12 12.20.42.000000000 PM AMERICA/NEW_YORK
when instead it should be 19-JUN-12 16.20.42.000000000 PM UTC
. Is there any way in Oracle to change this?
The time_zone is normally set at database creation time: SQL> create database . . . set time_zone='+00:00'; To change the Oracle time zone for an existing Oracle database, we change the time_zone parameter and then bounce the database.
Oracle converts the data to a TIMESTAMP WITH LOCAL TIME ZONE value. This means the time zone that is entered ( -08:00 ) is converted to the session time zone value ( -07:00 ).
TIMESTAMP WITH TIME ZONE literals The following format specifies a TIMESTAMP WITH TIME ZONE literal: TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM' For example: TIMESTAMP '2017-08-10 10:30:20.15 -07:00'
Oracle does note store the data in specified format. You can use SYS_EXTRACT_UTC to get TIMESTAMP in UTC Timezone.
Do you really need to change the data that is stored in the database? Normally, it's sufficient just to convert to a different time zone for display, i.e.
SELECT <<your_timestamp_column>> AT TIME ZONE 'UTC'
FROM <<your table>>
Of course, if you want to, you can also
UPDATE <<your table>>
SET <<your timestamp column>> = <<your timestamp column>> AT TIME ZONE 'UTC'
to change all the data.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With