Change date from UTC to EST

[kamal0475]

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. 

[Michael_Litz]

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

[Heinvandenheuvel]

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')

See also : https://www.sqlite.org/lang_datefunc.html

Hein.

[kamal0475]

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.

[Heinvandenheuvel]

>>> 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.