Database Writer with Date and Time column in KNIME 3.1

KNIME Analytics Platform
#1

Hi,

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.

#2

I have this morning reverted back to version 2.12.1 and I have no issue, returned to version 3.1 and the issue returns. Perhaps there is a bug in the way that KNIME 3.1 handles datetime?

#3

Hi Twul,

we didn't change the DateAndTimeCells for KNIME 3....

Let me check if one of the developers has an idea what might cause the problem.

Thank you for reporting it!

Iris

#4

Thanks Iris, that would be appreciated.

An extra bit of information: even if I change the SQL type to varchar, only the time and timezone are written into PostgreSQL. For some reason the database writer is only passing on these bits of the DateAndTimeCells to the database.

#5

Hi, no I am sorry, we could not reproduce this so far.

could you maybe send us a test workflow?

Best, Iris

#6

Was there any resolution to this ? I have a similar issue where I am writing a record to a Postgresql database and errors are returned of form 'Error while adding rows #nnn - #mmm, reason : Batch entry 0 INSERT into [tablename]...'

This worked OK in 12.2 but I don't want to revert really because updating to 3.1. solved another issue....

I tried changing the data type in the exported table from timestamp to varchar and it accepted this OK....

#7

Hi again, just to add.... I meant version 2.12 of course, but I just tried setting the SQL type to timestamp and tested in version 2.12. 

In version 3.1. the string written is (e.g.) 06:00:21.00000 + 00:00:00

In version 2.12 the string written for the same field is 2015-11-29 06:00:21.00000 + 00:00:00

So I concur with behaviour observed by TWUL....

#8

Hi,

we could finally reproduce the problem. The error only occurs if the date column does not contain milliseconds. If that is the case KNIME 3.x writes the column as time without day, month and year information which causes the error for timestamp columns. We will fix this problem with KNIME 3.1.2 which will be released until the end of this week.

As a possible workaround until the release of 3.1.2 you can add milliseconds to your string column e.g. using the String Manipulator to append .0 to each string and changing the date pattern to dd/MM/yyyy HH:mm:ss.SSS.

We are sorry for any inconveniences this error has caused you.

Bye

Tobias

#9

Hi,
I am having a similar issue writing datetime columns to PostgreSql. I am using Knime 3.7.2, and trying to write data from a csv to the database.

The datetime columns are strings in the format yyyy-mm-dd HH:mm:ss and I have converted to datetime using the String to Date&time node. It writes to the db fine, but when I re-import the data in Knime or try using it in Tableau, its a string.
I’ve also tried adding milliseconds as suggested in this post MySQL Writing DateTime Bug, but then String to Date&Time won’t work on this.
I’ve also tried changing the SQL types in the Database Writer node, but this also fails.
The csv file was originally generated through another knime workflow.

Any help much appreciated

#10

Hi there,

I guess it is written in the db as string and that is way you see it as a string in KNIME or Tableau afterwards. Or?

Info: The old DB framework (one which from KNIME version 4.0.0. has addition legacy in node names) only supports the legacy date&time type. Whereas the new DB framework (production ready from same above mentioned version) only supports the new types e.g. Local Time. Using String to Date&Time node will output new date&time format. Using String to Date/Time legacy will output legacy date&type format.

If possible I recommend to update KNIME and switch to new DB framework.

Hope this will help.

Br,
Ivan