Geek asks and answers

Change TIMESTAMP timezone component from TIMEZONE to Oracle

I have some data that is stored in a column TIMESTAMP(6) WITH TIMEZONEin Oracle but is stored in the wrong time zone. By convention, all timestamps in the database should be stored in UTC format, but this data was incorrectly saved as EDT. Actual values ​​are equivalent to the correct UTC value; the problem is that it is stored as 19-JUN-12 12.20.42.000000000 PM AMERICA/NEW_YORK, but instead it should be 19-JUN-12 16.20.42.000000000 PM UTC. Is there a way in Oracle to change this?

+3
source share
1 answer

Do you really need to change the data stored in the database? It is usually quite simple to convert to another time zone for display, i.e.

SELECT <<your_timestamp_column>> AT TIME ZONE 'UTC'
  FROM <<your table>>

, ,

UPDATE <<your table>>
   SET <<your timestamp column>> = <<your timestamp column>> AT TIME ZONE 'UTC'

.

+6

More articles:


All Articles