5

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?

Hank Gay
  • 70,339
  • 36
  • 160
  • 222

1 Answers1

8

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I don't control everywhere that reads the data, so it needs to be consistent with how it's done elsewhere. Thank you a ton, btw. You wouldn't believe the HEINOUS hack I had just finished prototyping to accomplish this (I nested something like 5 casting functions *shudder*). – Hank Gay Jun 19 '12 at 18:37
  • @Justin Cave- After doing this update will the new data that is inserted be in new time zone or only the existing data will be updated? – Abhishek kumar Jan 03 '13 at 18:52
  • @Abhishekkumar - The `UPDATE` statement would only affect existing data. If you want newly inserted data to be in UTC, you would need to convert your `timestamp` to UTC as part of the process of inserting that data. – Justin Cave Jan 04 '13 at 06:56