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

[shaheermecci]

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;

[petter · Accepted solution]

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.

[Peter_Cammaert]

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?

[petter]

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 ....

[shaheermecci]

Petter, thank you! Your approach solve the issue

Shaheer