Time offset Issue

[talendstar]

I have a Talend flow that read a single row of data, which contains the max date from the table in Snowflake.

I am able to take that date and write it SQL Server.

However the max date in Snowflake is not the same as what is being written to SQL Server table.

Talend is applying an offset to date that it is reading from Snowflake.

So I expect the date written to SQL Server as 03/14/2019. But what Talend write is 2019-03-13 20:00:00.000.

I have found various solutions on the forum but all them seem not to work for me.

Any thoughts on how to resolve this issue?

Thanks

[vapukov]

Hi

not very clear - what is your main error (problem)?

format or offset?

- format yyyy-MM-dd HH:mm:ss is default for MS SQL, and in any case internal date format is integer, so you must use formating function for change default date format in you SQL code or application

- offset - need to check columns format (support timezone or not) and timezones  on all - Talend, Snowflake, SQL Server

offset mistakes is always pain 🙂 especially because of some clients (an application which you use to visualize tables) - some of them automatically convert timezone to your local time and some not. 

[Anonymous]

If you transfer the data with the Date typed column the job does not change anything!

Please describe how you measure the time offset. I pretty convinced it has something to do with the settings of the source and/or target database time zone or the way different tools interpret the date as text to present it to you.

[Anonymous]

Please take what you are telling. A date or timestamp column in a database does not have any format!

The only topic where a pattern comes into the game is how the database interpret timestamps provided as text (e.g. in the SQL statement).

Also the pattern in the Talend schema has absolutely no effect until you render the date as text (e.g. in a tLogRow) or your read a text file and expect to convert it into a Date.

[talendstar]

Sorry if my original post wasn't clear or confusing.

Here is a bit more detail:

I am printing the row to the screen using tLogRow (after reading from Snowflake). There is only one row since I am only picking the row with max date.

Now this row is printing the date as (along with rest of the row):

2019-03-13

In Snowflake the max date for this same row is:

2019-03-14

After the tLogRow I am using tAggregateRow and applying max function on the date column and only storing that date value in the output column. So now I only have to deal with this column value and not the row. After tAggregateRow, I have another tLogRow, which prints out this:

2019-03-13T20:00:00 

Let's call date above "a".

Where is this timestamp coming from?

Here is the query that I ran in the Snowflake. It shows that actual date value along with three other timezones. It looks like that the value "a" matches with timezone Pago_pago. 

The situation above brings to light following questions:

question 1: why is Talend taking a date that is march 14 but showing march 13.

question 2: where is the timestamp coming from in "a".

question 3: how do I fix this?

Thanks.

[talendstar · Accepted solution]

I was able to get this to work by using the workaround in following post:

https://community.talend.com/t5/Design-and-Development/resolved-Convert-Historical-UTC-Time-in-Local...

I used this piece code to convert time to UTC and was able to get the correct date:

// template routine Java
package routines;
import java.util.Date;
public class UserDefined {
	static java.util.Calendar cal = java.util.Calendar.getInstance();
	static int zoneOffset = cal.get(java.util.Calendar.ZONE_OFFSET);
	static int dstOffset = cal.get(java.util.Calendar.DST_OFFSET);
    /**
     * convertUTCtoLocalTime: converts a UTC time given to local time
     * 
     * 
     * {talendTypes} Date
     * 
     * {Category} User Defined
     * 
     * {param} date type: The date to convert
     * 
     * {example} convertUTCtoLocalTime(sysdate) # returns date converted to local time
     */
	public static Date convertUTCtoLocalTime(Date date) {
		cal.setTime(date);
		cal.add(java.util.Calendar.MILLISECOND, (zoneOffset + dstOffset));
		return cal.getTime();
	}
	
    /**
     * convertLocalTimetoUTC: converts a local time given to UTC time
     * 
     * 
     * {talendTypes} Date
     * 
     * {Category} User Defined
     * 
     * {param} date type: The date to convert
     * 
     * {example} convertLocalTimetoUTC(sysdate) # returns date converted to local time.
     */
	public static Date convertLocalTimetoUTC(Date date) {
		cal.setTime(date);
		cal.add(java.util.Calendar.MILLISECOND, -(zoneOffset + dstOffset));
		return cal.getTime();
	}
}

Once you save code above in your repository>code (by creating a new routine) you can reference it (like you would reference Talend.parseDate).