Error is: “Execute failed: ERROR: type “timestamp_with_timezone” does not exist”
Knime 4.3.1. Using PostgreSQL connector and DBWriter with Zoned Date Time columns, trying to commit to table in PostgreSQL. Postgres has a timestamp with timezone type. Default behavior is to map this type to a string in the db. Not sure why since the driver should support it. For some reason Knime/jdbc driver fails with this error trying to map to the timestamp_with_timezone db type.
Repro:
Create a table with a Zone Date Time column and some data. Update PostgreSQL Connector mapping for Zoned Date Time to timestamp with timezone. Try to use a DB Writer to commit it to a new table. Should get this error.
Sure, sorry for the delay. Here you go… You can just spin up a docker for postgres and then connect with a user with create table rights on an example database. Please notice that I changed the type mapping for ZonedDateTime (Java/Knime) to timestamp_with_timezone (Postgres) in the DB Writer Node. This should work, as they are equivalent types, but the error is “Execute failed: ERROR: type ‘timestamp_with_timezone’ does not exist”. This is not actually so, as that is a valid Postgres type. A workaround is to let it go in as a string and then convert with a DB SQL Executor and some SQL like this:
ALTER TABLE public.test
ALTER COLUMN "dataPoints.values.intervalStartUtc" TYPE timestamptz
USING "dataPoints.values.intervalStartUtc"::timestamptz;
ALTER TABLE public.test
ALTER COLUMN "dataPoints.values.intervalEndUtc" TYPE timestamptz
USING "dataPoints.values.intervalEndUtc"::timestamptz
This is not the ideal scenario, of course. It appears that someone at Knime knows about this limitation, because the default mapping for ZonedDateTime is -->String–>VARCHAR:
Hello txjmb,
Postgresql does not properly support the timestamp with time zone type according to the documentation it is just an offset timestamp. The returned column does not contain the timezone itself.
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system’s timezone parameter, and is converted to UTC using the offset for the timezone zone.
When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct.
Because of the magic time conversion we do not use the timestamp with time zone type to represent ZonedDateTime in KNIME but rather varchar which doesn’t loose any information. You can also transform the zoned date time column in KNIME to UTC and then write it as timestamp into a timestamp with timezone column in Postgres as shown in the attached workflow which is based on your example workflow.
Bye
Tobias Postgres_Timestamp_w_Timezone.knwf (19.3 KB)
Thank you for the explanation. We worked around it in a similar way to your example. I understand now why this would be difficult to get a predictable conversion.
