tPostgreSQLInput Reading UTC timestamp incorrectly

Anonymous — Thu, 31 May 2018 19:04:37 GMT

I am trying to ETL timestamp values from one PostgreSQL database table to another. The timestamps in the source table are of type "timestamp without timezone" and are in UTC. The problem is that when I try to read values from March 11th, 2018 from 2AM to 3AM, the component tries to "fix" them by shifting them all up one hour, since that time range did not exist in Eastern Daylight Time. But since the original values are UTC, that range did indeed "exist". How do I stop the component from trying to "fix" them and just accept the values as read?

Re: tPostgreSQLInput Reading UTC timestamp incorrectly

vapukov — Fri, 01 Jun 2018 00:22:48 GMT

this is a huge pain! 🙂 and this is not a Talend problem - mostly JDBC do all them self

what I do on similar tasks where need convert timestamp without timezone (but UTC):
1) before open jdbc connection, use tJava for setup JVM timezone UTC

java.util.TimeZone user_TimeZone = java.util.TimeZone.getTimeZone("UTC");
java.util.TimeZone.setDefault(user_TimeZone);

2) in tPostgreSQLInput - edit query to show for JDBC - it is already UTC

"select
	timecolumn AT TIME ZONE 'UTC' // this is not necessary, but I prefer mange it
from table_source
"

3) in tPostbreSQLOutput component use TIMESTAMPTZ for output column

this is and only this combination allow to have finally:

vladimir=# select
vladimir-# timecolumn AT TIME ZONE 'UTC' as time_at_utc,
vladimir-# timecolumn time_local
vladimir-# from table_target;
     time_at_utc     |       time_local       
---------------------+------------------------
 2018-03-11 02:15:18 | 2018-03-11 15:15:18+13
(1 row)

topic Re: tPostgreSQLInput Reading UTC timestamp incorrectly in Talend Studio

tPostgreSQLInput Reading UTC timestamp incorrectly

Re: tPostgreSQLInput Reading UTC timestamp incorrectly