topic Re: Change date from UTC to EST in Qlik Replicate

Change date from UTC to EST

kamal0475 — Thu, 05 Oct 2023 17:50:05 GMT

We have a column CHG_DT in global transformations which is populating UTC time and we want it to be EST. Can you please help me.

Re: Change date from UTC to EST

Michael_Litz — Thu, 05 Oct 2023 18:01:44 GMT

Hi @kamal0475

You can use a date function to add or subtract the correct amount of hours to adjust it.

DATE($MyDate, '-4 hour')

Michael

Re: Change date from UTC to EST

Heinvandenheuvel — Thu, 05 Oct 2023 19:17:28 GMT

Using a hardcoded offset (-4 hours for EDT) is 100% the correct answers to the question asked.

However, the question asked is probably not the one intended.

It will be a problem with summertime/wintertime.

If the desired time zone (EDT) is in fact the local time zone then it is better to use: DATETIME('now', 'localtime')

Hein.

Re: Change date from UTC to EST

kamal0475 — Thu, 05 Oct 2023 19:49:21 GMT

Thank you both for your great response. Michael, yes your datetime works but as @Heinvandenheuvel mentioned we will have to look into the summertime/wintertime condition. And @Heinvandenheuvel DATETIME('now', 'localtime') is giving UTC as the source is in UTC I think. If you have any other option please provide, else we will discuss internally and get back to you. Thank you.

Re: Change date from UTC to EST

Heinvandenheuvel — Fri, 06 Oct 2023 03:43:03 GMT

>>> DATETIME('now', 'localtime') is giving UTC as the source is in UTC I think.

Don't think. That's too hard. Just try!

The input is assumed UTC and the modifier 'localtime' modifies it to ... the time in the local time zone.

Now if the local time zone (for the Replicate Server. Not _your_ local time) is not EDT but for example is westcoast PDT and has the same summertime rules as EDT then you should still use 'localtime' and further modify to add or subtract the fixed differential hour ( +3 for a server in the PDT )

Hein.