topic Re: [resolved] Set TimeZone in Talend Studio

[resolved] Set TimeZone

Anonymous — Tue, 03 Jun 2014 13:00:43 GMT

Hi,
I have a CSV that I'm importing to MySQL.
The time field is in 'Local Time'; however before I import to the table I need to convert it to UTC time.
My TimeZone is 5:30 UTC. Therefore I need to -5:30 from the mentioned time in the CSV and then import.
I tried this
https://community.talend.com/t5/Design-and-Development/resolved-Convert-Historical-UTC-Time-in-Local-Time-with-Daylight/td-p/111753
but it ain't correct. In fact I need to opposite of this.
Thanx in advance.
John

Re: [resolved] Set TimeZone

Anonymous — Tue, 03 Jun 2014 13:52:51 GMT

Pl check this post, you will get idea
https://community.talend.com/t5/Design-and-Development/Adding-the-local-Date-Time-without-Time-Zone/td-p/112900
If you are sure that you want to convert it to timezone is UTC, then why not to just add -5.30 to existing timestamp?
what is the format of your incoming date?
Vaibhav

Re: [resolved] Set TimeZone

Anonymous — Tue, 03 Jun 2014 19:51:14 GMT

The JVM will try to determine the correct TZ on it's own. On Windows, this is pretty straight forward. On Linux, there are several ways (locale,etc) and it can be hit or miss. Simply set the JVM timezone yourself in the load job, eg:
System.setProperty("user.timezone","America/Chicago");
java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("America/Chicago"));
Then write the values to a MySQL TIMESTAMP column, this way MySQL will convert and store it in UTC for you. It will convert it back to the TZ of the local JVM when querying through JDBC, all magical like.

Re: [resolved] Set TimeZone

Anonymous — Wed, 04 Jun 2014 06:39:27 GMT

Thanks for the replies; I kind of modified the existing code that I had looked up here.
https://community.talend.com/t5/Design-and-Development/resolved-Convert-Historical-UTC-Time-in-Local-Time-with-Daylight/td-p/111753
So, my date/time format is thus
2012-01-19 16:26:16
I needed to convert the time to -5:30
Therefore the time (prior to import) needs to be this
2012-01-19 10:56:16
Below is the code I used, successfully.
================================================
package routines;
import java.util.Date;
public class F9322 {
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);
public static Date convertUTCtoLocalTime(Date date) {
cal.setTime(date);
cal.add(java.util.Calendar.HOUR_OF_DAY, -5);
cal.add(java.util.Calendar.MINUTE, -30);
return cal.getTime();
}
}
=================================================
Hope it helps anyone who has the same problems.
John