Issue Loading Date/Time values Into Postgresql Database

One Star

Issue Loading Date/Time values Into Postgresql Database

When I load records into a database with a date/time field that is sometimes null I get this wierd behavior where the date loads properly, but the time is set to midnight (00:00:00). This only happens when there are some null values for the date/time and I have verified this. If I send a date/time value in every record the issue goes away. When I put a tLogRow after my tPostgresqlOutput component the date/time is displayed correctly.

My thought is that the issue lies somewhere in how the tPostgresqlOutput component updates the database. It seems like if there are null dates in a block of records that are being committed the issue pops up. I have not tested on any other database though.

Does anyone have any ideas on whats causing this issue?

Thanks in advance.
Community Manager

Re: Issue Loading Date/Time values Into Postgresql Database

Hello
What's the data type of column? Date or timestamp? What's your expected result in Postgresql table? I done a simple test and it display empty in my table.
You can try the following expression:
row1.mydate==null?null:row1.mydate

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Issue Loading Date/Time values Into Postgresql Database

****Please note I have registered since my first post...I am the same person that asked the orginal question.

The data type in the destination database table is "Timestamp without time zone."

In this particular example I am converting the date/time from a string to a java.util.Date value using a custom Java function and then pushing the value into a "Timestamp without time zone" field. I have verified by debugging that the issue is not triggered in my custom function and I have recreated the same behavior moving a "Timestamp without time zone" value from one table to another.

I have attached 2 images showing the data I am working with. This first is the resulting data in the destination table and the second is the source data for the timestamp . As you can see from the images, the first 4 timestamps seem to load correctly, while the rest have the time set to midnight with the correct date. I have also posted a screenshot of my tMap component.

I also tried the code you suggested and it did not seem to make any difference.
Community Manager

Re: Issue Loading Date/Time values Into Postgresql Database

Hello
From your images, I see you don't define the date pattern on schema of output table. If you still have problem, please export your job and send it to me via email.

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Issue Loading Date/Time values Into Postgresql Database

I sent information on this to our email address. Please let me know if you did not receive.

Thanks...
One Star

Re: Issue Loading Date/Time values Into Postgresql Database

Hi
We have the same error, and even put the correct pattern don't correct the issue....

any ideas?

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch Now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog