Migrating Oracle DATE columns to TIMESTAMP with timezone

Question

Bakground: I've got a legacy app I'm working on that uses DATE types for most time storage in the database. I'd like to try update some of these tables so that they can utilize time zones since this is causing problems with users in different areas from where the db is(see A below). This is for Oracle 10g.

Quetions:

1) Can I migrate this "in place." That is can I convert like so

DATE_COL = type:DATE   =>    DATE_COL = type:TIMESTAMP

...or will I have to use a different column name?

Keep in mind that data needs to be retained. If this can be done semi-easily in a migration script it will work for my purposes.

2) Will this type of conversion be backwards compatible? We likely have some scripts or reports that will hit this table that we may not know about. We can probably deal with it but I'd like to know what sort of hornet's nest I'm walking into.

3) What pitfalls should I be on the lookout for?

Thanks,

EDIT:
(partly in response to Gary)

I'm fine with a multi-step process.

1) move data to a new Timestamp column (caled TEMP) with some sort of conversion 2) drop old column (we'll call it MY_DATE) 3) create new timestamp column with the old date column name (MY_DATE) 4) move data to the MY_DATE column 5) drop TEMP column

A Gary also wanted clarification on the specific timezone issue. I copied my answer from below to keep it more readable.

Basically the data will be accessed from several different areas. We need to be able to convert to/from the local time zone as needed. We also have triggers that use sysdate further complicating things. timestamp with time zone alleviates a lot of this pain.

Oh and thanks for the answers so far.

Answer 1

Simple enough to demonstrate

SQL>  create table x (y date);
Table created.
SQL> insert into x select sysdate from dual;
1 row created.
SQL> commit;
Commit complete.
SQL> alter table x modify y timestamp;
Table altered.
SQL> select * from x;

Y
---------------------------------------------------------------------------
03/NOV/09 12:49:03.000000 PM
SQL> alter table x modify y date;
Table altered.
SQL> select * from x;
Y
---------
03/NOV/09
SQL> alter table x modify y timestamp with time zone;
alter table x modify y timestamp with time zone
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
SQL> alter table x modify y timestamp with local time zone;
Table altered.
SQL> alter table x modify y date;
Table altered.

So you can go from date to timestamp (or timestamp with local timezone) and back again, but not for timestamp with time zone (ie where the offset is persisted). You'd have to add another column, and copy the existing data over (with a default for the appropriate time zone).

"causing problems with users in different areas from where the db is". Might help to be a bit more specific. Is it sufficient to convert the dates (or timestamps) from the database timezone to the user's timezone when inserted/changed/queried, or do you actually need to persist the fact that the record was created at 3:00pm in a specific timezone.

Answer 2

You could just run:

ALTER TABLE your_table MODIFY your_date_column TIMESTAMP WITH TIME ZONE;

But I would recommend adding a TIMESTAMP column to the table, using an UPDATE statement to populate, and drop the original date column if you so choose:

ALTER TABLE your_table ADD date_as_timestamp TIMESTAMP WITH TIME ZONE;

UPDATE your_table
   SET date_as_timestamp = CAST(date_column AS TIMESTAMP WITH TIME ZONE);

The conversion is backwards compatible - you can switch back & forth as you like.