Oracle TIMESTAMP WITH TIME ZONE datatype.
Posted: Fri May 04, 2007 5:21 pm
I am saving a TIMESTAMP into a TIMESTAMP WITH TIME ZONE column in an Oracle stage. However, I want to
A) Store it in a different time zone than the default.
B) Preserve the Time Zone when extracting the data and moving to other Oracle databases.
Does anyone have suggestions? Here is what I've been able to find and try with limited success:
DataStage apparently does not recognize or support the "WITH TIME ZONE" capability, and the Oracle stage does not seem to honor the "Additional Connection Option" to modify the default Time Zone for a connection (ORA_SDTZ = 'DB_TZ' | 'OS_TZ' | '[+|-]HH:MI' | 'timezone_name'). I also tried to add ALTER SESSION code to the Insert statement in the Oracle stage (ALTER SESSION SET TIME_ZONE = '-2:00'; ) but that fails at run time, I think due to the ";".
I have considered three workarounds and combinations thereof, each with drawbacks:
1) Convert to UTC when extracting the data. Unfortunately, the original time zone information is permanently lost at that point unless I carry it through as a separate column.
2) Carry the DateTimes as strings in DataStage. This preserves the Time Zone but requires lots of converting if any time manipulation is required, and at that point maintaining the original time zone is complex.
3) Experiment with UNIX $TZ environment variables. This may help with actually inserting the data into Oracle, but doesn't help with internal DataStage manipulation.
On a related note, will DataStage Version 8x do a better job with TIMESTAMP/TIME ZONE support?
A) Store it in a different time zone than the default.
B) Preserve the Time Zone when extracting the data and moving to other Oracle databases.
Does anyone have suggestions? Here is what I've been able to find and try with limited success:
DataStage apparently does not recognize or support the "WITH TIME ZONE" capability, and the Oracle stage does not seem to honor the "Additional Connection Option" to modify the default Time Zone for a connection (ORA_SDTZ = 'DB_TZ' | 'OS_TZ' | '[+|-]HH:MI' | 'timezone_name'). I also tried to add ALTER SESSION code to the Insert statement in the Oracle stage (ALTER SESSION SET TIME_ZONE = '-2:00'; ) but that fails at run time, I think due to the ";".
I have considered three workarounds and combinations thereof, each with drawbacks:
1) Convert to UTC when extracting the data. Unfortunately, the original time zone information is permanently lost at that point unless I carry it through as a separate column.
2) Carry the DateTimes as strings in DataStage. This preserves the Time Zone but requires lots of converting if any time manipulation is required, and at that point maintaining the original time zone is complex.
3) Experiment with UNIX $TZ environment variables. This may help with actually inserting the data into Oracle, but doesn't help with internal DataStage manipulation.
On a related note, will DataStage Version 8x do a better job with TIMESTAMP/TIME ZONE support?