25 May 2022

ConvertToLocalTime()

Share this message

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:

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? And remember:

  • If you have suggestions for questions, we love to hear from you via WhatsApp or at info@bitmetric.nl
  • If you’re enjoying these questions and want to work on stuff like this every day (but a bit more challenging), we’re always on the lookout for new colleagues. Check our job openings here.

How can we help?

Feel free to contact us if you have any comments or questions.