TimeZone handling in TIBCO Jaspersoft® ETL

Issue Description [toc on_off::hide=1]

Suppose you have a job which should be able to map several source database columns to the destination columns in an Oracle DB.  There are several columns that are TIMESTAMP(6) WITH LOCAL TIME ZONE.  You need to write to the destination database in two ways - The first is a direct mapping from the source database - Both columns are the same type (TIMESTAMP(6) WITH LOCAL TIME ZONE).

So if you pass the following value from the source Database and pass it through the job you would expect the value to be the same in the destination database.  This is provided the session timezone is the same for each database.  You have also been setting the session timezone to UTC(00:00) before beginning the copying operation as well as setting the JVM timezone to be UTC.

The second requirement is that the time the job ran must be written to each row in the destination database.  This value must be passed in externally via a context parameter.  You are doing this by parsing the String input into a date using SimpleDateFormat with the following pattern - dd-MM-yyyy_HH:mm:ssXXX. This would parse a string such as... 07-06-2016_14:00:00+01:00 Where the +01:00 value is the offset.  This parameter may have a timezone offset that is different to the session timezone in the destination database.

For example, if you pass this value into the destination database and the session timezone is 00:00, you would expect that the column would contain the following value... 07-06-2016 13.00.00.

It would appear that we cannot achieve both requirements. Setting the session timezone means that the offset value we pass in(in the case of our second requirement) is ignored.  However if we do not set this(despite the timezones being the same by default anyway), the source value will be one hour ahead of the destination. 

Resolution

You can solve this problem by setting the session time zone of both databases and setting the JVM time zone.

Ref. Case 00069378