PostgreSQL connector handling of timezone

hi All,
I am using the PostgreSQL connector to connect to a database with a timestamp column. The type is TIMESTAMP in the database when i inspect this column in DBeaver. Now after I read this column with the PostgreSQL connector (not the legacy, but the latest), this column is converted to local datetime as configured in the input type mapping of the PostgreSQL connector.
My question is: is it possible to configure this conversion to a given time zone (e.g. CET+1). Now I can only choose LocalDatetime → Local Date Time for Timestamp mapping. It is not possible to specify a mapping to a given timezone.
Thanks,
Frederik

Hi @fstouten2,

PostgreSQL has two different timestamp types: TIMESTAMP and TIMESTAMP WITH TIME ZONE
Depending on the type KNIME will convert it either to Local Date Time or Zoned Date Time, so currently it is not possible to convert TIMESTAMP to Zoned Data Time. An option would be to either change the type of the column in the data base or add the timezone afterwards by using the Modify Time Zone node after you pulled the data into KNIME using the DB Reader.

Best,
Julian

2 Likes