topic Re: Convert date/time from UTC to Local time based on country code or Time Zone in Talend Studio

Convert date/time from UTC to Local time based on country code or Time Zone

Anonymous — Sat, 16 Nov 2024 05:45:53 GMT

Hello,

I have flights departure time that are stored in Oracle Database in UTC date & time.

I have a table with airport code, associated country and associated time zone.

Do you know a solution to convert date and time from UTC to Local based on available information ?

I can read on the community that some manage similar issue having the time difference between local & UTC ... I do not have such information ...

Difference between UTC time and Local time is influenced by TimeZone, but also by time change Summer/Winter in some countries.

All ideas are welcome.

regards

Re: Convert date/time from UTC to Local time based on country code or Time Zone

TRF — Thu, 23 May 2019 09:53:11 GMT

Hi Damien,

You need to create a routine like this one for this purpose:

package routines;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.TimeZone;

public class dateConversion {

	 public static String convertTzToTz(String strDate, String inTz, String outTz) throws Exception
	    {
			if (strDate == null || inTz == null || outTz == null)
					return null;
			 
			// Convert strDate from any valid timezone such Europe/Paris to another one
			// strDate is expected to be formatted as "yyyy-MM-ddTHH:mm:ssZ"
			 
			SimpleDateFormat indfm = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");
			indfm.setTimeZone(TimeZone.getTimeZone(inTz));
			Date inDate = indfm.parse(strDate);
	
			SimpleDateFormat outdfm = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");
			outdfm.setTimeZone(TimeZone.getTimeZone(outTz));
	
			return outdfm.format(inDate);
	    }
}

Then in your job you can use the following expression to convert from GMT to Paris TZ:

dateConversion.convertToGmt(out1.CreatedDate, "GMT", "Europe/Paris")

Of course, timezone can be expressed depending on your own data (row1.inTimezone, row1.outTimezone).

If you need more detail, contact me by PM (french speaking if you prefer).

Re: Convert date/time from UTC to Local time based on country code or Time Zone

Anonymous — Sat, 25 May 2019 13:28:03 GMT

Hello TRF,

thank you for your answer.

I will try it ... but I am a beginner ... java code in talend job ... new for me.

just a question concerning the piece of code you are sharing with me.

On top we declare

convertTzToTz

and then you ask me to use

convertToGmt

a typo ?

then, my objective is not only to convert from GMT to Local, knowing that in some places in the world (like France where I am), we have winter/summer time. Is this piece of code taking it into account ?

I come back to you when I succeed to implement it.

Thanks again for you answer.

Damien

Re: Convert date/time from UTC to Local time based on country code or Time Zone

Anonymous — Sat, 25 May 2019 14:12:24 GMT

hello,

I tried but something is wrong with the date ... date format ?

you mentionned that you expect specific date format in input ... yyyy-mm-ddThh:mm:ssZ ? what are T & Z ?

Démarrage du job Test_Home_UTC_Local a 15:07 25/05/2019.
[statistics] connecting to socket on port 3415
[statistics] connected
Exception in component tMap_1 (Test_Home_UTC_Local)
java.text.ParseException: Unparseable date: "2019-05-01 05:55:00"
at java.text.DateFormat.parse(Unknown Source)
at routines.dateConversion.convertTzToTz(dateConversion.java:19)
at servair_rim.test_home_utc_local_0_1.Test_Home_UTC_Local.tFileInputExcel_1Process(Test_Home_UTC_Local.java:1594)
at servair_rim.test_home_utc_local_0_1.Test_Home_UTC_Local.tWarn_1Process(Test_Home_UTC_Local.java:463)
at servair_rim.test_home_utc_local_0_1.Test_Home_UTC_Local.runJobInTOS(Test_Home_UTC_Local.java:2195)
at servair_rim.test_home_utc_local_0_1.Test_Home_UTC_Local.main(Test_Home_UTC_Local.java:2044)
[statistics] disconnected

Job Test_Home_UTC_Local terminé à 15:07 25/05/2019. [Code sortie=1]

Re: Convert date/time from UTC to Local time based on country code or Time Zone

TRF — Sat, 25 May 2019 14:38:31 GMT

Hi,

On my side I have 2 different methods, one for conversion from any TZ to GMT (convertToGmt) and another to convert from any TZ to any TZ (convertTzToTz). As the 2nd one is more general, I've give you just this one. Of course it takes care of daylight saving time (summer/winter time) for any place in the world.

Regarding the exception you encounter, be carefull to the expected inout date format which should be "yyyy-MM-ddTHH:mm:ss".

Re: Convert date/time from UTC to Local time based on country code or Time Zone

Anonymous — Sat, 25 May 2019 14:47:18 GMT

Thank you TRF.

it seems to work ! 2 hours ago I never played with java code in talend ...

As I convert airport time departure and arrival from UTC to local, I use the timezone as a parameter.

It's perfect ... I continue my testing.

Have a good week end.

Damien

Re: Convert date/time from UTC to Local time based on country code or Time Zone

TRF — Sat, 25 May 2019 15:16:38 GMT

Great!

Don't hesitate if you need more help or a contact as an integrator to help you on your projects.

Re: Convert date/time from UTC to Local time based on country code or Time Zone

Anonymous — Mon, 27 May 2019 07:55:19 GMT

Bonjour,

The code do not work for Morroco ... you may know that in 2018 that have decided last minute, not to change time summer/winter ... else (for other countries), it works well.

regards

Damien

Re: Convert date/time from UTC to Local time based on country code or Time Zone

TRF — Mon, 27 May 2019 11:04:44 GMT

The code use the TimeZone.getTimeZone method which depends of the JRE version which doesn't always take care of the latest time zone data.

It seems this is a well known problem as explained here https://stackoverflow.com/questions/27925035/wrong-offset-for-timezone-casablanca-java

Re: Convert date/time from UTC to Local time based on country code or Time Zone

Anonymous — Tue, 18 Jun 2019 17:39:17 GMT

HI I am also facing same issues could you please help me in this , if i am adding the format as "yyyy-MM-dd HH:mm:ss" then i can able to view data but if i am changing the format as yyyy-mm-ddThh:mm:ss i am not able to succed .here attaching the screen shot for your reference

Re: Convert date/time from UTC to Local time based on country code or Time Zone

Anonymous — Tue, 18 Jun 2019 17:44:45 GMT

HI I am trying to implement your idea but got below error while modify the pattern from source ,could you please help me in this

Re: Convert date/time from UTC to Local time based on country code or Time Zone

TRF — Tue, 18 Jun 2019 18:54:59 GMT

2 things to be checked:

input date is expected to be formatted as "yyyy-MM-ddTHH:mm:ssZ"
timezone must be a valid one (from a Java point of view) such as https://garygregory.wordpress.com/2013/06/18/what-are-the-java-timezone-ids/