Values in a column of type 'Timestamp with timezone' are different from original values when imported through Information Links

Values in a column of type 'Timestamp with timezone' are different from original values when imported through Information Links

When using Information Links to import data, the values in the columns of type 'Timestamp with Timezone' might differ from the actual values of the data sources. This is a list of a few example databases and the corresponding column data types which involve time zones:

• PostgreSQL: TIMESTAMPTZ, TIMETZ, TIMESTAMP WITH TIMEZONE
• Snowflake: TIMESTAMP_TZ
• Oracle: TIMESTAMP WITH TIME ZONE

When accessing data via an Information Link, the JDBC driver on the TIBCO Spotfire Server is used to access data. JDBC sets it's client machine timezone as the default session timezone to us so when using a JDBC connection, for some of the data sources, columns of type Timestamp with timezone get the time value based on the TIBCO Spotfire Server timezone. So time values from the column data types mentioned above which involve timezones are adjusted to the time zone JDBC sets as default, which in this case is TIBCO Spotfire Server timezone. The time difference seen between the data in the database and after being accessed in Spotfire will be the difference of the database server timezone and the TIBCO Spotfire Server timezone. This is the expected behavior.

For example:

Assuming a PostgreSQL database server has a timezone of UTC and the TIBCO Spotfire Server machine has a timezone "US/Pacific". If in the original column in the database, the value is '2019-02-28 15.30.30.00 000' then the value you will see in Spotfire when imported through Information Links will be of UTC-8 type. So the exact value seen in this situation will be '2019-02-28 07:30:30.00'.