Hi, I need to do migration date->timestamp with timezone similar to described here: http://stackoverflow.com/questions/1664627/migrating-oracle-date-columns-to-timestamp-with-timezone. But I need to make additional convertion (needed to work correctly with legacy apps): for all dates we need to change timezone to UTC and set time to 12:00 PM. So now dates are stored in local database (New York) timezone. I need to convert them this way

25/12/2009 09:12 AM (local timezone) in date column => 25/12/2009 12:00 PM UTC timestamp with local timezone column.

Could you advice, how to set timezone for date value in Oracle (I found only suggestions how to convert from one timezone to another) (for example in Java there is setTimeZone method for Calendar objects).

We want to make a convertion this way:

1. rename old date column to NAME_BAK
2. create new column timestamp with local timezone
3. iterate over old column for not-null values set timezone to UTC, time to 12:00 PM
4. drop old column after testing of this migration