Write Date&Time with Database Writer to PostgreSQL DB

KNIME Analytics Platform
1

Hi all,

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?

Br,
Ivan

2

Hi,

Try 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.

Martin K.

3

Hi Martin,

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.

Br,
Ivan

4

Hi Ivan,

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 !

Martin K.

5

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.

1 Like
6

Hi Martin!

It makes sense now :slight_smile:

I found a workaround for time being so I will skip legacy node and wait for new release :wink:

Tnx,
Ivan

7

Hi Iris!

Does this mean there is no Data Type in Table Creator node which corresponds to Date&Time data type in database (timestamp in PostgreSQL)?

Br,
Ivan

8

Hi @Iris

is there an info for this one maybe? This is taking care of with new database integration nodes?

Br,
Ivan

9

Hi @izpazin

This is only possible with our new database integration. For this you can already find a preview in KNIME Labs.

Best wishes, Iris

10

Ok. Tnx!
And it is ipazin :smiley:
Br,
Ivan

11

Hi, Iris, where is the preview version? I can not find it in KNIME Labs. Thanks!

12

Hi Have,

you need to install it first.You can find here a description how to install additional extensions into KNIME:

https://www.knime.com/downloads/update

Best wishes, Iris

13

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.

14

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.