Adjust timezones in Qlik with ConvertToLocalTime()

Have you ever had to adjust timestamps in UTC time to your local timezone? Then ConvertToLocalTime() is just the function you need. This article explains how it works.

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about converting timezones in Qlik:

A company in Helsinki mentions to their Data Architect that a phonecall heatmap is not corresponding with the office opening hours (08:00 to 20:00). Which solution should be used to properly transform the field?

The correct answer is C: ConvertToLocalTime(PhoneCallTime, ‘Helsinki’)

When extracting data from various sources, it is most likely that the server on which the data is stored runs on UTC time. This will mean that if any timestamps are stored they will be stored in UTC time as well. When this data is subsequently loaded without accounting for different time zones, differences between the stored timestamp and the actual time on which an action has happened may occur, as is the case in the example. Since the opening hours of the office are between 08:00 and 20:00 the majority of the phonecalls is to be expected to be between those hours. In the figure however, we can clearly see they start to early and end too soon. By using the following syntax we can solve this:

ConvertToLocalTime(timestamp [, place [, ignore_dst=false]])

The first part of the syntax requires the timestamp which we would like to convert. In this case it was the field “PhoneCallTime”.
The second part of the syntax is ‘Place‘ or rather the location we would like to convert to. So in the example we have used Helsinki. Hereby it is worth noting that Qlik retrieves its location information from the Windows Registry. You can find the whole list of locations and time zones at:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones

Keep in mind however that this part of the syntax is sensitive to the language settings on the machine. Cities may have different spelling in different languages, so make sure hat the spelling used in the script matches the language settings of the machine.
Daylight savings time. Standard this is set to False (0), but by setting this to True (1) you will be able to ignore DST if this is required. This is also the reason that answer D is wrong. While technically this might work, it does not account for DST. Helsinki and Johannesburg for example are both in the same timezone, UTC+2. However as we can see in this example, the current time for Helsinki is UTC+3 since Finland uses DST, while Johannesburg does not. So when using ConvertToLocalTime also keep in mind whether the location you are converting to is located on the northern or southern hemisphere to make sure this is also calculated correctly.

That’s it for this week. See you next Friday?