I’m trying to write date and time to Postgre database. In my workflow I’m using Date&Time Input node that produces flow variable type string in following format ‘yyyy-mm-ddT hh:mi:ss.S’. Now I would like to write it down to Postgre database using Database Writer node. I can write it down as a string (character varying) but I would like to have it as a timestamp. Any ideas?
Variable to Table Row node (flow variable -> table row) and then
String to Date/Time node. I have tried to write output on Portable PostgreSQL and it worked for me.
the problem is that time is written to PostgreSQL as a varchar not a timestamp. In KNIME you got data type Local Date Time which does not match timestamp from postgre database.
Sorry - I forgot to mention that this was tested on an older Knime version and actually
String to Date/Time (legacy) node was used. It outputted Date and Time datatype value (not Local Date Time compared to
String to Date/Time node), which was written into PostreSQL column of timestamp without time zone datatype. Hope this helps !
The current database integration only supports the legacy date and time cells. With the new release there will be a preview of the new database integration which does support the new date and time cell.
For the time being you can convert using the the date&time to legacy date&time node.
It makes sense now
I found a workaround for time being so I will skip legacy node and wait for new release
Does this mean there is no Data Type in Table Creator node which corresponds to Date&Time data type in database (timestamp in PostgreSQL)?
is there an info for this one maybe? This is taking care of with new database integration nodes?
This is only possible with our new database integration. For this you can already find a preview in KNIME Labs.
Best wishes, Iris
And it is ipazin
Hi, Iris, where is the preview version? I can not find it in KNIME Labs. Thanks!
you need to install it first.You can find here a description how to install additional extensions into KNIME:
Best wishes, Iris
Thank you for this - It’s been so frustrating trying to diagnose the error message.
Wish they would’ve mentioned that on the database connector nodes.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.