Oracle Data Integrator (ODI): Timestamp with local timezone and daylights saving time

Published on October 16, 2010
Updated on November 20, 2024

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
If you are you using the Oracle data type TIMESTAMP WITH LOCAL TIMEZONE in your data warehouse, you need to be careful when you are loading data with ODI.
Let’s assume you have set the time_zone on your database server to UTC, which corresponds to GMT.

In such a scenario, you need to set the time_zone of your client to the time_zone that corresponds to your geographical area, e.g. for myself this would be EUROPE/DUBLIN as I am based in Dublin, Ireland.
Note that this time_zone is different from GMT, as daylights saving time is observed in Dublin. During the summer months Dublin is one hour ahead of GMT.
Let’s have a look at an example in SQL+.
In SQL+ there are two ways to correctly set your client time_zone.
You can set environment variable ORA_SDTZ = EUROPE/DUBLIN or alternatively you can

Please note that SQL Developer picks up your time_zone from the Operating System (in Windows set under Date and Time) and ignores/overrides ORA_SDTZ.
To identify the time zone of your current session:

The above output shows us the timestamp in 7 byte format. The last three bytes represent the time component of the timestamp. There is one small caveat here. The output is offset by 1. So we need to subtract 1 from the output, which gives us 23:00:00. You may wonder why it is 23:00:00 and not 00:00:00. This is because the date we inserted is in dalights saving time (EUROPE/DUBLIN, Irish Summer Time) and the database translates that into the UTC time zone: 00:00:00 IST equals 23:00:00 UTC on 12 OCT.
Let’s continue

This displays the timestamp in the time_zone of the client (Europe/Dublin)

IST = Irish Summer Time

This gives us the offset between IST and UTC: one hour
Let’s change our local time zone

The offset then changes to -7.
You may wonder how this is relevant for ODI.
Well, you need to correctly set the client’s time zone when inserting with ODI. Your client is either your agent or the Designer or Operator module.
Typically this is done in the odiparams.bat file.
Open odiparams.bat and search for variable ODI_ADDITIONAL_JAVA_OPTIONS
set ODI_ADDITIONAL_JAVA_OPTIONS=”-Duser.timezone=”Europe/Dublin” ”
Set this variable to your time zone. If you are using the agent as a Windows service you need to re-install it.
When you now insert a timestamp into your database the correct time is recorded
insert into ts1 values (timestamp’2010-10-12 00:00:00.000′);
Alternatively you can specify the time zone in the timestamp directly which would override the above variable, e.g.:
insert into ts1 values (timestamp’2010-10-12 00:00:00.000 Europe/Dublin’);
More on timestamp data type, e.g. an explanation on the offset in the byte representation of the timestampe can be found in Tom Kyte’s excellent book Expert Oracle Database Architecture: Oracle Database Programming 9i, 10g, and 11g Techniques and Solutions, Second Edition, which is now available in in a second edition also covering Oracle 11g.

Uli Bethke

Uli Bethke

Uli has been rocking the data world since 2001. As the CEO of Sonra, the data liberation company, he’s on a mission to set data free. A self-proclaimed data evangelist, Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker. Uli is a co-founder of DAMA Ireland, helping build a thriving community of data enthusiasts, and a proud Snowflake Data Superhero, leading the charge as the chapter lead of the Snowflake User Group Ireland. Whether sharing insights, empowering others, or championing the coolest data tools, Uli makes data fun. Growing up in Germany and making Ireland his home, Uli brings a global perspective to everything he does. On the academic side, he holds degrees in political science from Freie Universität Berlin, Albert Ludwigs Universität Freiburg, and the University of Ulster, Coleraine, adding a unique interdisciplinary flair to his work.

LinkedIn