Convert UTC to ETC

[trsisme05]

I need help converting a UTC timestamp to EST. 

The data is currently formatted as 2020-05-17 17:45:35.6524922 +00:00.

Ideally I would like to format as M/D/YYYY hh:mm:ss TT.

I have tried many variations of the ConvertToLocalTime with no luck, such as ConvertToLocalTime([EntryDateTime],'GMT-5:00'). Do I need to include the original formatting of the timestamp and the formatting I want within the function? I have read the other UTC forums, but they are very basic.

[Nicole-Smith · Accepted solution]

Qlik probably isn't recognizing your date field as a date, so we need to parse out the date/time with subfield, let Qlik know it is a date using date#, and then convert:

ConvertToLocalTime(DATE#(SUBFIELD('2020-05-17 17:45:35.6524922 +00:00', '.', 1), 'YYYY-MM-DD hh:mm:ss'), 'Eastern Time (US & Canada)')

I would suggest adding the subfield and date# in your load script instead of within the expression itself.  However, I did test what I have above and it works.

[trsisme05]

Perfect! Thank you! This is such a huge help.

How does QlikView know the original is in UTC when converting to local?

[Nicole-Smith]

The ConvertToLocalTime() function assumes the original date is in UTC.  If it's in something else, you would need to use different logic.