
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Convert date/time from UTC to Local time based on country code or Time Zone
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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".

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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".

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Great!
Don't hesitate if you need more help or a contact as an integrator to help you on your projects.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- « Previous Replies
-
- 1
- 2
- Next Replies »