Oracle Data Integrator (ODI): Timestamp with local timezone and daylights saving time
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.
|
1 |
ALTER DATABASE SET time_zone = 'UTC'; |
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
|
1 |
ALTER SESSION SET time_zone='EUROPE/DUBLIN'; |
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:
|
1 |
SELECT SESSIONTIMEZONE FROM dual; |
|
1 2 3 4 5 6 7 8 9 10 |
SQL> CREATE TABLE ts1 ( 2 ts1 timestamp(0) with local time zone 3 ); Table created. SQL> insert into ts1 values (timestamp'2010-10-12 00:00:00.000'); 1 row created. SQL> SELECT DUMP(ts1) AS dump_ts1 FROM ts1; DUMP_TS1 ---------------------------------------- Typ=231 Len=7: 120,110,10,11,24,1,1 |
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
|
1 2 3 4 |
SQL> SELECT ts1 FROM ts1; TS1 ------------------- 12-OCT-10 00:00:00 |
This displays the timestamp in the time_zone of the client (Europe/Dublin)
|
1 2 3 4 |
SQL> SELECT extract(TIMEZONE_ABBR from ts1) tz_reg FROM ts1; TZ_REG ---------- IST |
IST = Irish Summer Time
|
1 2 3 4 5 |
SQL> select extract(timezone_hour from ts1) tz_h from ts1 2 ; TZ_H ---------- 1 |
This gives us the offset between IST and UTC: one hour
Let’s change our local time zone
|
1 2 3 4 5 6 |
SQL> alter session set time_zone = 'US/PACIFIC'; Session altered. SQL> select extract(timezone_hour from ts1) tz_h from ts1; TZ_H ---------- -7 |
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.
