topic Re: Convert UTC to ETC in QlikView

Convert UTC to ETC

trsisme05 — Sat, 16 Nov 2024 18:23:48 GMT

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.

Re: Convert UTC to ETC

Nicole-Smith — Wed, 08 Jul 2020 21:55:46 GMT

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.

Re: Convert UTC to ETC

trsisme05 — Thu, 09 Jul 2020 12:05:00 GMT

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

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

Re: Convert UTC to ETC

Nicole-Smith — Fri, 10 Jul 2020 15:55:51 GMT

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