1

Why does this show the same time twice:

=Now() & ' ' & TimeZone() & '\n' & ConvertToLocalTime(Now(), 'UTC') & ' UTC'

This shows:

2017-04-08 07:53:27 GMT-08:00
2017-04-08 07:53:27 UTC

I'm trying to conditionally-format a pivot table expression based on whether its start timestamp is "in the future". The field in question is correct (ConvertToLocalTime([Start Time], 'UTC') shows the correct timestamp), and the local time is also correct, but I cannot accurately compare my local non-UTC time with another from a different time zone.

r2evans
  • 141,215
  • 6
  • 77
  • 149

2 Answers2

1

From the helpfile you will notice this is to convert a UTC or GMT timestamp to local time as a dual value

So usage would be as follows:

=ConvertToLocalTime(UTC(), 'GMT-05:00')

For your example:

=ConvertToLocalTime(UTC(), TimeZone() ) & ' ' & TimeZone() & '\n' & UTC() & ' UTC'

Helpfile source: http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/converttolocaltime.htm

Shaun
  • 559
  • 1
  • 3
  • 17
  • This was a great step to resolving some of the question, foremost that ConvertToLocalTime requires (and is documented as such) a UTC/GMT timestamp to do anything. It was very helpful in inferring that Qlik does not include "time zone" within a timestamp. Thank you! – r2evans Apr 20 '17 at 17:50
0

Part of the problem is that I had mis-read the portion of the ConvertToLocalTime docs, namely that it requires a GMT or UTC timestamp to operate. (I'm accustomed to other programming languages where the timezone is stored in or with the timestamp and can adjust automatically.)

Another aspect to this (not stated in the original question) is that QV is relying on the underlying OS for some timezone information, and I believe that Windows (ergo both QV desktop and QV server) is doing something odd, I think incorrect: despite the fact that UTC does not observe daylight savings time, Windows provides different times for "UTC" and "UTC+00:00". (If there is a valid explanation for this behavior, please comment!)

ConvertToLocalTime(UTC())                      : 2017-04-20 10:12:17
ConvertToLocalTime(UTC(), "UTC")               : 2017-04-20 17:12:17
ConvertToLocalTime(UTC(), "UTC+00:00")         : 2017-04-20 18:12:17 (huh?)
ConvertToLocalTime(UTC(), "UTC+00:00", "True") : 2017-04-20 17:12:17

(Double quotes are shown above for the text, single-quotes for the actual function call.) ConvertToLocalTime() has a third argument, ignore_dst which may be used to mitigate this, but because it's a nuclear option that cannot be safely applied to non-UTC timezones, I cannot programmatically deal with time zones without several other checks.

Bottom line: QV does not store the time zone with a timestamp. That is, it relies on the programmer to track the source of the timestamp and do conversion as needed. This suggests that if time zones are ever used or in question, all data should be stored in "UTC" proper (not "UTC+00:00") so that ConvertToLocalTime() can be used to easily convert into other zones. (Many suggested TZ best practices apply here.)

Related: not all database timestamp types are supported by QV. For example, SQL Server's datetimeoffset is not supported, so manual parsing is required in order to preserve time zone and correctly use the numeric/timestamp dual.

Community
  • 1
  • 1
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • Maybe give this a shot as well, it seems the ConvertToLocalTime and LocalTime functions in QV is buggy. So try to create your own, did a quick one like the following: Create a variable with the following expression `Timestamp(UTC()+$1/24)` then call the variable as follows `$(vToTimezone(+2))` or `$(vToTimezone(-2))` in my example the var was called vToTimezone and the value I am passing **+2 / -2** is for GMT+02:00. You can do additional coding in the var to suit your needs to actually accept inputs from datetimeoffset – Shaun Apr 20 '17 at 19:38
  • One issue is that I'm processing ~1M SQL rows where the timestamps in one field will have differing time zones. Since I can't know them ahead of time, I parse the string, extract the time zone, and do something similar. I used `SET DateTimeOffset2Serial=Timestamp#(left($1, 27)) + Num#(left(right($1, 5), 2))/24` based on SQL Server's `datetimeoffset` data type. Thanks for the comment! – r2evans Apr 20 '17 at 19:41
  • 1
    Just as a note, QV does accept timezones within the timestamp, see here `=Timestamp(Replace(Replace(Replace('2017-04-20 20:50:43.456 +02:00',' +','+'),' -','-'),' ','T'))` that is to make a `datetimeoffset` to a QV timestamp – Shaun Apr 20 '17 at 20:05
  • Nice suggestion. I benchmarked them and could not find a significant difference in calc-time. It seems your approach might be a little more robust in that it does not rely on string length; besides that, are there any (perhaps QV-centric) advantages to the triple-`Replace` over the `left/right` function? – r2evans Apr 20 '17 at 21:07
  • The only real reason I used the replace x3 is to first replace the space between milliseconds and timezone, then space between date and time. You could use the left right as well but think it will be much longer code. – Shaun Apr 21 '17 at 06:53