Set current time as a value of DB column of type timestamp

I have data loaded from source table (On-premise Oracle 12c) and I’m supposed to write it to target table (PostgreSQL 10.5). The structure of the tables is the same except that target table contains several columns that don’t occur in the source table. The one I need your help with is a timestamp value. It’s supposed to be a time when data is loaded. I don’t want current timestamp of source database which is the reason I don’t use the simplest way like select src.*, current_date as sa_timestamp, … Instead I need to set the column value in my workflow. The problem is I can’t insert it into target table without annoying messages like:

ERROR Database Writer 2:2 Execute failed: java.lang.RuntimeException: Column “sa_timestamp” of type “Local Date Time” from input does not match type “timestamp” in database at position 17
ERROR Database Writer 2:2 Execute failed: java.lang.RuntimeException: Column “sa_timestamp” of type “Zoned Date Time” from input does not match type “timestamp” in database at position 17
ERROR Database Writer 2:2 Execute failed: java.lang.RuntimeException: Column “sa_timestamp” of type “String” from input does not match type “timestamp” in database at position 17

I tried using Date & Time Input node with “Use execution time” option checked.
I tried changing db query to “select src.*, ‘$${Scurrent_timestamp}$$’ sa_timestamp …” subsequently with no success.
I tried using String to Date/Time node too.
Each attempt resulted in error message listed above.

I’d appreciate any advice. Please note changing type of the target column is the last option I’d rather avoid.

Thanks,
Jan

Hi Jan!

You are encountering same problems as I did :smiley:

Current database integration nodes support old Date&Time type so use node Date&Time to legacy Date&Time. Should do the trick :wink:

With Knime 3.6 version there are new database integration nodes but still not for production…

Br,
Ivan

2 Likes

Hi Ivan,

thank you again.

The bad thing is I can’t get the timestamp column visible in the Date&Time to legacy Date&Time node I’ve added to my workflow so the node doesn’t convert anything. I guess the reason is KNIME keeps considering my column a String value as I can see when I display “Input table with additional columns” or “Converted table” window using node’s local menu. I’m pretty sure the type should be rather DateTime but I can’t achieve it.
The important part of workflow is as follows:

Here is what I can see in Date&Time to legacy Date&Time node configuration:

And this is what I get if I click “Converted table” Date&Time to legacy Date&Time node’s local menu item:

image

sa_timestamp is the variable I configured my Date&Time input node to fill:

Any hint please?

Hi!

Date&Time Input node creates String variable so Date&Time to legacy Date&Time won’t do it. Use String to Date/Time (legacy) with proper date format.

Br,
Ivan

1 Like

Hi Ivan,

This works. Perfect!

Thank you.

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