CODEWITHSUNDEEP
oracle-database
Mobilemike
Mobilemike

Reputation: 37

Update TIMESTAMP value using WHERE to determine time zone

A developer has written data to my database that has the incorrect timezone format. Instead of an hours offset it has the name as seen here:

18-NOV-16 12.17.41.000000000 PM -05:00
18-NOV-16 12.17.05.000000000 PM -05:00
18-NOV-16 12.16.07.000000000 PM AMERICA/NEW_YORK
18-NOV-16 12.12.54.000000000 PM -05:00
18-NOV-16 12.12.33.000000000 PM -05:00

The column in question is of type TIMESTAMP(0) WITH TIMEZONE. Another 3rd party application is crashing reading these records as it apparently is not coded to handle the long-form timezone.

Researching here, I have found the correct update query to correct this:

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

The problem is I have 500K+ records and don't want to update the entire table when only a few hundred rows are involved (due to replication that takes place after the update). How would I use a WHERE clause to only get the rows that are stored with the inappropriate timezone data? I've read all over and can't find an example that shows filtering results by the format of the timezone.

Thank you for your help!

Upvotes: 0

Views: 69

Answers (1)

Renato Afonso
Renato Afonso

Reputation: 654

I don't have data with me to test, but try this:

where extract(timezone_region from date_column) <> 'UNKNOWN'

I think that when you use this function, and it only has number value (not the region) it returns you 'UNKNOWN'

Upvotes: 3

Related Questions