Change timezone component of TIMESTAMP WITH TIMEZONE in Oracle

Question

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?

Answer 1

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.