topic Re: Date offset issue while writing to postgresql in Talend Studio

Date offset issue while writing to postgresql

Anonymous — Sat, 16 Nov 2024 02:17:32 GMT

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.

Re: Date offset issue while writing to postgresql

akumar2301 — Fri, 29 May 2020 12:49:10 GMT

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

What is your expected output?

Re: Date offset issue while writing to postgresql

Anonymous — Fri, 29 May 2020 13:05:21 GMT

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.

Re: Date offset issue while writing to postgresql

akumar2301 — Fri, 29 May 2020 13:15:29 GMT

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.

Re: Date offset issue while writing to postgresql

Anonymous — Fri, 29 May 2020 13:47:27 GMT

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

Re: Date offset issue while writing to postgresql

tornilleiro — Sat, 30 May 2020 01:12:34 GMT

Dear sreevik,

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

Re: Date offset issue while writing to postgresql

Anonymous — Wed, 03 Jun 2020 08:40:41 GMT

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

Re: Date offset issue while writing to postgresql

tornilleiro — Wed, 03 Jun 2020 21:51:36 GMT

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.