Daylight saving time handling in tPostgresql components

Four Stars

Daylight saving time handling in tPostgresql components

Hi All,

 

I am facing issues when I am using tPostgresql components,is there any option to handle day light savings time in Postgresql components,here is my flow and brief description.

Flow: tPostgresql input (sql statement to check max(timestamp))-->tJavarow(assigning max(timestamp) to variable))--> tInformix input (source) in where clause using max(timestamp variable))-->tmap-->tPostgresql output.

Source: Informix

Target: Postgresql

Load runs every 15 minutes.

I see data issue when I check the data counts, i.e for example only 90 records are loaded into target out of 100 in source. and this happens at random intervals. like for example loads run at 12 am,12:15 am,12:30 am, and so on, I see data issue only for 2:00 am interval and again rest for the interval's data load is fine. I assume there is issue with day light saving time handling in Postgresql components as load triggers based on the last load time stamp sql function used in tPostgresql input components.

 

Note: I am using v9.x in tPostgresql db components  and my db version is 10.x 

 

Thanks.

 

Employee

Re: Daylight saving time handling in tPostgresql components

Hi,

 

    Please verify the database timestamps are exactly same for both source and target DBs.

 

    If possible, try to make the clock exactly same for both DBs. If you are having doubt related to Timezone, add the timezone also in the date format so that you can avoid any time zone related issues.

 

    If you cannot make the server timestamps in sync for both source and target, see the difference and pick the records which are 1 hour older instead of current system time stamp. Sometimes, you will have issues for border records when you are giving exact system time. If you are not having any issues related to time zone, a safe bet will be to extract the records which are 5 min older than system time.

 

     If the system is mission critical, then you will have to make sure that all your system time for all different DBs are referring to single global time instead of local time settings.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

Four Stars

Re: Daylight saving time handling in tPostgresql components

Thank you
Employee

Re: Daylight saving time handling in tPostgresql components

@vignesh_bha1993 

 

If you are happy with the details given, could you please mark the topic as closed? You need to select the posts which helped you to arrive at the solution and submit to close the topic.

 

This will help other Talend community members while doing reference.

 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved :-)

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

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog