topic Re: QlikView: FileTime() function does not account for Daylight saving time in QlikView

QlikView: FileTime() function does not account for Daylight saving time

shaheermecci — Tue, 28 Nov 2017 18:55:56 GMT

Hello,

I am reading old and newer files and extracting their file attributes from a Windows folder. Files are from Oct and Nov 2017.

Daylight saving ended 11/5/2017.

Reading files from prior to 11/5 using FileTime() function returns time that is 1 hour off. Files from 11/5 forward return the correct time.

How can I account for DST and get the time of the file shown in Windows folder? Or is there a better way to extract the same info?

I am using QlikView desktop version 11.20 SR12

Thanks!

Shaheer Mecci

My code below.

FileList:

Load * INLINE [FolderName,FileName,FileDateTime,FileSize];

Sub GetFiles (vFolderLoc, vFolderName)

for each Files in FileList(vFolderLoc)

Concatenate (FileList)

LOAD

FolderName, FileName, FileDateTime, FileSize;

LOAD

'$(vFolderName)' as FolderName,

SubField('$(Files)', '\', -1) as FileName,

Timestamp(FileTime('$(Files)')) as FileDateTime,

FileSize('$(Files)') as FileSize

Autogenerate 1;

next Files;

for each SubDirectory in dirlist(vFolderLoc)

call GetFiles(SubDirectory);

next SubDirectory

End Sub;

LET NoFileTypes = NoOfRows('TEMP_Files'); //Reference Table with folder name & file pattern to look for.

For vRow = 1 to NoFileTypes

LET vFolder = peek('Folder', vRow-1, 'TEMP_Files');

LET vFolderLocationName = peek('FileLocation', vRow-1, 'TEMP_Files'); //This is the wildcard value for 'folder\file.extension'

CALL GetFiles (vFolderLocationName, vFolder);

Next vRow;

Re: QlikView: FileTime() function does not account for Daylight saving time

petter — Tue, 28 Nov 2017 23:03:24 GMT

Actually the time functions in a load script suffer from the same problems as Visual Basic and other Windows languages when using the normal Windows API. They don't take Daylight Saving Time into consideration.

So the expression to determine the date of autumn standard date is:

Date(MakeDate(vYear,11,1)+6-Num(WeekDay(MakeDate(vYear,11,1)-1,6)))

And for the spring DST date is:

Date(MakeDate(vYear,3,1)+13-Num(WeekDay(MakeDate(vYear,3,1)-1,6)))

Then you will have to add the time the change will occur which I believe is 02:00AM

Lastly you will have to compare these datetimes to add the DST hour in summer but not in winter.

A modified load script might look like this:

// DST as observed in the USA

SET fDST = Date(MakeDate($1,3,1)+13-Num(WeekDay(MakeDate($1,3,1)-1,6))+2/24) ; // The second Sunday of March

SET fStd = Date(MakeDate($1,11,1)+6-Num(WeekDay(MakeDate($1,11,1)-1,6))+2/24) ; // The first Sunday of November

LOAD

FolderName,

FileName,

If( FileDateTime >= $(fDST(Year(FileDateTime))) AND FileDateTime < $(fStd(Year(FileDateTime))) , FileDateTime + 1/24 , FileDateTime ) AS FileDateTime,

FileSize

;

LOAD

'$(vFolderName)' as FolderName,

SubField('$(Files)', '\', -1) as FileName,

Timestamp(FileTime('$(Files)')) as FileDateTime,

FileSize('$(Files)') as FileSize

Autogenerate 1;

I haven't fully tested this code but it should be pretty close to something that would work for you.

Re: QlikView: FileTime() function does not account for Daylight saving time

Peter_Cammaert — Wed, 29 Nov 2017 00:30:55 GMT

I was thinking along another path, but got stuck (and ran out of time). It may offer automatic application of DST adjustments..

QlikView has a great function for converting Timestamps from UTC to Local time with or without DST (with, I think in this case). I guess that one would fix the problem of not having DST applied to FileTime() results.

How to get the Last Modified time for a Wiondows file? NTFS appears to store all file DateTime values (created, last modified, last accessed) in UTC anyway, so that would be the ideal feed for ConvertToLocalTime()....

Unfortunately, during my 10-minute google search for a technique to obtain NTFS raw values in VBScript, I couldn't find a solution, let alone a hint about what api to look into. Any ideas?

Re: QlikView: FileTime() function does not account for Daylight saving time

petter — Wed, 29 Nov 2017 06:38:59 GMT

This article goes a long way explaining how to do it with a WMI helper object called SWbemDateTime:

https://technet.microsoft.com/en-us/library/a579d1bd-05c7-4620-abf4-8e21d0a0be0c#Figure 2 A Readable Install Time

So it is possible to create a VBScript function that takes advantage of this. But then again you have to write a Macro ....

Re: QlikView: FileTime() function does not account for Daylight saving time

shaheermecci — Wed, 29 Nov 2017 14:11:29 GMT

Petter, thank you! Your approach solve the issue

Shaheer