Date offset issue while writing to postgresql

[Anonymous]

Hi, I have a talend standard job which I am using to read few records from hive and write them to postgresql. But while writing all my dates are getting offset my 8 hours.

Original date: 2020-05-29 00:00:00
Date in postgresql: 2020 -05-28 16:00:00
I have kept a tlogrow just before the target write in which the date is coming correctly without any offset.
The hive server is in MYT ( Malaysia time zone) and postgresql which is aws rds is in UTC. Kindly let me know on any possible solutions.

[akumar2301]

It is normal as it is same time in different timezone. 

What is your expected output?

[Anonymous]

Hi Abhiskek thank you for the reply.
The expectation is to write the date as it is without doing any offset. Is it normal for talend to do automatic offsetting here.

[akumar2301]

Yes , actually it is normal.below time is same , but in different timezone. 

Original date: 2020-05-29 00:00:00 MYT 
final : 2020 -05-28 16:00:00 UTC

I assume that you are running your job in MYT timezone machine. If you change it to UTC , you will get same time. or you need to add offset(+8) hours to input datetime. 

[Anonymous]

Yes our talend job servers are in MYT. Will try out this idea.

[tornilleiro · Accepted solution]

Dear sreevik,

You can add the following setting directly in advanced settings to set the UTC time. 

[Anonymous]

tornilleiro,

Wow, this worked like a charm. Thank you. It solves the problem. But I have the below questions. Please let me know your thoughts.
1) We have many Talend jobs, but only this job has an issue. All the settings in this job is an exact replica of other jobs.
2) Many date columns are being read in this job from the hive table. Only some columns have the date offset issue. All the date target fields are of timestamp without timezone type.

Regards
Sreekanth

[tornilleiro]

Hi Sreekanth

Regarding the 1) I am assuming that the other jobs that you are executing they are created in the same Talend and they are executed to load the same Postgresql. Is the date field parsed or transformed during the data flow?. Many elements can make the difference, difficult please see what I going to describe now, may be it helps you

Regarding 2) and in general. Working with dates in Talend and in general with any tool when several timezones are involved is tricky. You have to have clear in which timezone is working the origin, in which timezone are working the libraries of your jobs and to which timezone you want to populate.  Sometimes also some SQL clients try to use your local setting to show the time values according to your regional values.Then, the  value that you see in the screen using your client is different than what is stored :-).

To figure out what is happening I normally use, during development phase,  the Talend function  TalendDateformat(<date>,"yyyy-MM-dd HH:mm:ssZ") to see as string the date that I am retrieving. This give you clues about the timezone and what your JVM is interpreting.  You can make a test inserting on your destination or in your origin DB  the value of the string and the proper time format in a dummy table. Later,  you can compare  in client side the string value and the time value. 

I don't know a general rule. I hope it helps!. Enjoy playing with dates.