I'm encountering an issue in KNIME 3.1 that I had not previously encountered in past versions of KNIME 2.x.
My workflow goes something like this:
- File Reader (three columns: a string ID, a double value and a string datetime)
- Rule-based Row Filter (remove missing values)
- String to Date/Time (convert string timestamps dd/MM/yyyy HH:mm:ss to 'Date and Time' type)
- Joiner (to bring in an integer replacement for the string ID)
- Database Writer (connected to a PostgreSQL Connector, to write into a new table)
I have tried timestamp, timestamp without time zone and timestamp with time zone as SQL types in the Database Writer configuration but I receive the following error every time, for every row:
WARN Database Writer 2:82 Error while adding row #10 (Row8), reason: ERROR: invalid input syntax for type timestamp: "02:00:00.000000 +00:00:00"
WARN Database Writer 2:82 Error while adding row #11 (Row9), reason: ERROR: invalid input syntax for type timestamp: "02:15:00.000000 +00:00:00"
WARN Database Writer 2:82 Error while adding row #12 (Row10), reason: ERROR: invalid input syntax for type timestamp: "02:30:00.000000 +00:00:00" - more errors...
WARN Database Writer 2:82 Errors "68891" writing 68891 rows.
The date part is clearly being omitted on submission to PostgreSQL! That, or PostgreSQL is ignoring it, but my instance of PostgreSQL has not been modified in the past few months.
Has anyone else encountered this? Does anyone know why this might be happening? I've removed steps and replicated previous successful workflows in KNIME and they also seem to fail in the same way when run in KNIME 3.1. I am yet to try reverting back to 2.12 for further testing.
Many thanks to anyone able to help.