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?
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)