Oracle: set timezone for column

Question

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

Answer 1

Hi dbf,

You might want to read the documentation about Oracle datatypes:

• a DATE column doesn't have a timezone, this information simply isn't stored with this datatype.
• a TIMESTAMP WITH LOCAL TIME ZONE column has the same time zone offset as the database. You can't specify a different offset for a column of this type.

It differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This datatype is useful for displaying date information in the time zone of the client system in a two-tier application.

If you want to store the time zone you will have to use the datatype TIMESTAMP WITH TIME ZONE.

You would convert a date to a timestamp with the to_timestamp_tz function for example:

SQL> WITH DATA AS (
  2     SELECT to_date('25/12/2009 09:12 AM', 'DD/MM/YYYY HH:MI AM') dd FROM dual
  3  )
  4  SELECT dd,
  5         to_timestamp_tz(to_char(dd, 'YYYYMMDD')||' GMT', 'YYYYMMDD TZR') tz
  6    FROM DATA;

DD          TZ
----------- -------------------------------------------------
25/12/2009  25/12/09 00:00:00,000000000 GMT

Answer 2

Creating and populating the new column ...

SQL> alter table t23
  2      add new_col timestamp(3) with time zone
  3  /

Table altered.

SQL> update t23
  2      set new_col = col3
  3  /

7 rows updated.

SQL> select to_char(new_col,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') new_col
  2  from t23
  3  /

NEW_COL
----------------------------
22-MAR-2010 03:20:58.000 PST
21-MAR-2010 03:20:58.000 PST
20-MAR-2010 03:20:58.000 PST
19-MAR-2010 03:20:58.000 PST
18-MAR-2010 03:20:58.000 PST
17-MAR-2010 03:20:58.000 PST
16-MAR-2010 03:20:58.000 PST

7 rows selected.

SQL> 

So now to set the values of COL3 to NOON UTC, or GMT as we Brits (and Oracle) know it:

SQL> alter session set time_zone = 'GMT'
  2  /

Session altered.

SQL> update t23
  2      set col3 =
  3          cast(to_char(col3, 'DD-MON-YYYY')||'12:00:00' as timestamp) at time zone sessiontimezone
  4  /

7 rows updated.

SQL>

Let's see the outcome:

SQL> alter session set time_zone = 'PST'
  2  /

Session altered.

SQL> select to_char(new_col,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') as orig_val
  2         , to_char(col3,'DD-MON-YYYY HH24:MI:SS.FF3 TZR') as upd_val
  3  from t23
  4  /

ORIG_VAL                     UPD_VAL
---------------------------- ----------------------------
22-MAR-2010 03:20:58.000 PST 22-MAR-2010 12:00:00.000 GMT
21-MAR-2010 03:20:58.000 PST 21-MAR-2010 12:00:00.000 GMT
20-MAR-2010 03:20:58.000 PST 20-MAR-2010 12:00:00.000 GMT
19-MAR-2010 03:20:58.000 PST 19-MAR-2010 12:00:00.000 GMT
18-MAR-2010 03:20:58.000 PST 18-MAR-2010 12:00:00.000 GMT
17-MAR-2010 03:20:58.000 PST 17-MAR-2010 12:00:00.000 GMT
16-MAR-2010 03:20:58.000 PST 16-MAR-2010 12:00:00.000 GMT

7 rows selected.

SQL>

All that's left to do is to drop the backup column...

SQL> alter table t23 drop column new_col
  2  /

Table altered.

SQL>

Although, if it is a big table you may prefer to set it to UNUSED and then drop it in slow time.