topic Re: When am I? (What timezone setting need to be synchronized in order to collect file times?) in QlikView

When am I? (What timezone setting need to be synchronized in order to collect file times?)

evan_kurowski — Fri, 25 Sep 2015 20:46:26 GMT

Hello Community,

I have a question regarding time zone settings and the relationship between the present, operating system times, and the QlikView settings/functions. While trying to form an application that used file modification times as part of the keying system, I got the following results:

Settings:

My Windows operating system is set to the following time zone (with Daylight savings checked):

(UTC-05:00) Eastern Time (US & Canada)

Results when scanning a file from a local Drive:

Windows Explorer shows file modification time of 5:00PM
QlikView FileTime() function reports as 5:00PM

Results from scanning files on a network server:

All file modification times collected from QlikView FileTime() function are increased by +1 hour from those shown in Explorer
(5:00PM in Explorer, reports as 6:00PM in QlikView FileTime() function)

Results of functions used in QV Interface (using the 8:00 GMT as an example reference point):

website reports GMT of 8:00PM
Now() = 4:00PM // (matching system clock of -5GMT Easter Standard Time)
ConvertToLocalTime(Now(),'GMT') = 5:00PM //reports 5:00PM +1 hours - Atlantic Time
ConvertToLocalTime(Now(),'Eastern Time (US & Canada)') = 12:00PM //reports -4 hours Alaska

So my question is “When am I”? Based on the above, it appears the results are being placed in 3 different time zones at once. What settings can be synchronized in order to consistently collect file times aligned to a single time zone designated the “time point of reference”?

Re: When am I? (What timezone setting need to be synchronized in order to collect file times?)

evan_kurowski — Sun, 04 Oct 2015 16:51:25 GMT

I guess if this is an issue that can't be ironed out, maybe I'll revise the situation.

When collecting flat files from any possibility of sources, is there a way I can form an identifier based on file attributes to check whether the file has been modified from the last time I attempted to read information from it?

The premise here was, if the file name & modification time is unchanged, you don't need to reload this data.

Any ideas?

Re: When am I? (What timezone setting need to be synchronized in order to collect file times?)

marcus_sommer — Mon, 05 Oct 2015 07:36:07 GMT

I could think to read the timezone settings from the server registry - Time Zone registry key? - per qv-function:

GetRegistryString(pfad, key)

and then to compare this information with the timezone from the qv system and then to add the differences to the timestamp or put them into a new field. A maybe easier workaround could be before reading the timestamps to store a dummy-file on this server and read afterwards the timestamp from server and compare it with the timestamp from storing.

Seems to be rather ugly workarounds but within a sub-routine or an include-variable it shouldn't clutter the code very much.

- Marcus

Re: When am I? (What timezone setting need to be synchronized in order to collect file times?)

evan_kurowski — Tue, 06 Oct 2015 16:44:10 GMT

Hello Marcus,

This is an interesting suggestion. I followed your link to the Microsoft Technet page where the thread describes setting a registry key 'ActiveTimeBias'.

Time Zone registry key?

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation

I think I'll be creating an attribute for ActiveTimeBias, creating groups using this attribute, and then I can schedule scripts against these groups accordingly.

Before I could contemplate what effect setting ActiveTimeBias has on the QlikView FileTime() function, or what I should set it to, I took a look at the registry values themselves.

Something strange occurred and now I have another question. When I examined the registry keys in regedit everything looks clean, but when I read the same registry area in Powershell, the key 'TimeZoneKeyName' seems to have a lot of additional content embedded after the string 'Eastern Standard Time'. Any ideas on what is going on with that strange looking registry key?

**Update:

I tried changing the value of 'ActiveTimeBias' from 240 to 0. My QlikView function ConvertToLocalTime(Now(1), 'Eastern Time (US & Canada)') is off by 4 hours (240 minutes), so I was hopeful this would re-align things, however chaning the registry keys in this area hasn't had a noticeable impact on QlikView functions (that I'm aware of).

Tried instructions here for setting the CMOS clock to UTC, in conjunction with creating the registry var RealTimeIsUniversal.

https://technet.microsoft.com/en-us/sqlserver/ff794720(v=winembedded.60) However, after reboots still could not sync the QV functions.

Re: When am I? (What timezone setting need to be synchronized in order to collect file times?)

marcus_sommer — Wed, 07 Oct 2015 08:16:47 GMT

The difference between regedit and powershell could be caused by a multi-row entrie into this key and which regedit didn't display in this window. Also the qv function GetRegistryString(pfad, key) read only the first row - I had have already trouble with this.

Maybe my second workaround-suggestion with a dummy-file and the differences between this store-timestamp and the file-timestamp is easier to use.

- Marcus

Re: When am I? (What timezone setting need to be synchronized in order to collect file times?)

evan_kurowski — Thu, 08 Oct 2015 13:47:22 GMT

Hello Marcus, Thanks for your input on this.

Let me see if I'm understanding this strategy correctly. I create a local file, and I know the timestamp is 5:15pm. This file is moved out to other network locations as a "time marker".

I run a scan of this same file while it resides on a network location, and FileTime() returns 3:15pm, therefore I can discern an offset of -2 hours for all content during that parse. Is that how this is supposed to work?

As for the multi-line registry entries only visible in certain clients, that seems like a confusing concept! It opens up new questions about how to consistently read a registry entry. Thanks again for your suggestion though, it seems like a workable option.

Re: When am I? (What timezone setting need to be synchronized in order to collect file times?)

marcus_sommer — Thu, 08 Oct 2015 15:01:22 GMT

Hello Evan,

yes, this is excactly what I meant with my dummy-file workaround (create local or on the network-store - what better worked).

To read multi-row registry keys you could use vbs like:

set objRegistry = CreateObject("Wscript.shell")

Key = objRegistry.RegRead(vUserInfo)

- Marcus