I am connecting to a Postgres database and in the table, there is a column called month_timestamp, created simply DATE_TRUNC(‘month’, date) without timezone.
However, KNIME recognizes this field as TIMESTAMP_WITH_ZONE.
To change this, I wanted to configure Input Type Mapping in DB Table Selector as attached above, however, the column type is still Zoned Date Time.
Lastly, the field values should be like this for March 2020: 01/03/2020 00:00:00
As you can see, it’s way different in Knime. How can I resolve this issue?
can you post the SQL statement of the DB Table Selector node here? You can copy it from the DB Query tab of the out port view. For what ever reason Postgres reports the column as timestapmtz type as shown in the DB Spec tab of the out port view that you have attached above.
I noticed that in my query GENERATE_SERIES function generates months with timezone (TIMESTAMPTZ) for month_timestamp column.
Hence I had to explicitly cast to timestamp, “without time zone”.
Now, everything is fine.
Thanks for the information. I’m glad you could solve the problem.