Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Convert UTC to ETC

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.

Labels (2)
1 Solution

Accepted Solutions
Highlighted

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.

View solution in original post

3 Replies
Highlighted

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.

View solution in original post

Highlighted
Contributor
Contributor

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

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

Highlighted

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