topic Re: tMap Expression Function to convert UTC to local time in Talend Studio

tMap Expression Function to convert UTC to local time

Edith1 — Wed, 07 Oct 2020 16:50:33 GMT

Hello, thanks in advance for reading and helping me out with the following question.

In Data Catalog, there's an attribute of "last modified date" and it shows both the Local Time and UTC Time. When I pull this data using the Data Catalog API, I'm only getting the UTC time. Is there a function or expression in tMap to convert the UTC to local? In the below picture, the API pulls 2020-10-05 22:26:32. Is there a way to convert this to 15:26:32? I was looking at the Talend Date functions, but not sure how to use them. Would tformatDateLocale() work?

Here's a picture of my job.

Re: tMap Expression Function to convert UTC to local time

DataTeam1 — Wed, 07 Oct 2020 17:59:57 GMT

@Edith Murillo I think you can use this function: TalendDate.addDate(yourUTC_Date, -7, "HH") to get 7 hours earlier time then yourUTC_Date.

Re: tMap Expression Function to convert UTC to local time

Edith1 — Wed, 07 Oct 2020 22:17:27 GMT

Thanks for the reply. I did get this to work, however, as I was testing the date against the application I came across dates that are difference of -8 instead of -7. The daylight savings is -7, but how do I incorporate when we're not in daylight savings? How do I code, IF DAYLIGHT SAVINGS then TalendDate.addDate(yourUTC_Date, -7, "HH") else TalendDate.addDate(yourUTC_Date, -8, "HH") ?

Re: tMap Expression Function to convert UTC to local time

cadap — Thu, 08 Oct 2020 13:17:48 GMT

Hi,

that's the reason why the Timezones exist.

Personally I use the "new" Java DateTime classes LocalDate and LocalDateTime, which of course can be converted to a Java "Date" (which Talend uses), because it is much simpler to work with.

For instance:

ZoneId zoneId = ZoneId.of("UTC") or ZoneId zoneId = ZoneId.of("Europe/Berlin")

gives you the ZoneId of the corresponding TimeZone and "knows" about the daylight saving time.

If you take the TimeZones into the date to string conversion, you will get the correct time for this timezone.

Exampe:

LocalDateTime localDateTime = LocalDateTime.now(zoneId); //actual time in the defined timeZone

You can of course convert ever date or datetime with a zoneId.

Re: tMap Expression Function to convert UTC to local time

Edith1 — Thu, 08 Oct 2020 17:36:56 GMT

Is there a function in tMap to convert the UTC into the local time?

If I format the date as "Z" in tMap, then I get the -0800 or -0700 (timeZone column below)

If I format the date "yyyy-MM-dd HH:mm:ss.SSSXXX", then I get the date and the time zone (convertedDate column)

Is there a way to use this? Subtract the timeZone from the originalDate to get the local time?

Re: tMap Expression Function to convert UTC to local time

Edith1 — Thu, 08 Oct 2020 19:11:58 GMT

There's a solution to do this with tMap date functions!

TalendDate.parseDateInUTC("yyyy-MM-dd HH:mm:ss", row1.Value )

The above expression is converting the originalDate to the newColumn, which is correct according to the time zone.