:::REQUEST::: File Dates

[bottomleypotts]

It seems file creation and modification dates are modified on Microsoft systems when daylight savings begins. Is there some way to obtain FileInfoToArray or any of the file dating function to return UTC time in order to bypass this issue?

[stanl]

Not sure if this helps but WB has functions such as FileTimeSet(), FileTimeGetEx().... which permit obtaining and changing file times. For conversion to UTC you could use the TimeZone bias as a parameter.

Code (WINBATCH) Select Expand



activebias = RegQueryDWORD( @REGMACHINE,"SYSTEM\CurrentControlSet\Control\TimeZoneInformation[ActiveTimeBias]")
machinegmtoffset = int ( ( activebias / 60 ) * ( -1 ) )
Message("TimeXone Bias",machinegmtoffset)


That would require your script to iterate desired files, obtain time(s), apply bias and and re-save as GMT.  I could be wrong but I remember years ago there was a user who worked for the Red Cross and carried laptop into different time zones but wanted all messages he wrote to be set to UTC, and the bias was used.

[stanl]

forgot to mention, you can also use WB's CLR and the ToUniversalTime() .net method. I think it has been discussed on the forum and some code even sent in.


I currently have this situation. I query an API that produces Json data with beginning/ending times in UTC format but set to PST. I have to convert to EST with acceptable format for placing in Excel and calculating duration. It relies on the .net DateTime Object. The point is what you are asking is possible but maybe not as easy as you would wish.


[EDIT]:  here is some quick code for CLR conversion

Code (WINBATCH) Select Expand



;Convert WIL YMDHMS to a DateTime Object
;Current Time in WIL format
date = TimeYmdHms()
arrYMDHMS = Arrayize( date, ":" )
;Create CLR Objects
objYear = ObjectClrNew( "System.Int32", arrYMDHMS[0] )
objMonth = ObjectClrNew( "System.Int32", arrYMDHMS[1] )
objDay = ObjectClrNew( "System.Int32", arrYMDHMS[2] )
objHour = ObjectClrNew( "System.Int32", arrYMDHMS[3] )
objMin = ObjectClrNew( "System.Int32", arrYMDHMS[4] )
objSec = ObjectClrNew( "System.Int32", arrYMDHMS[5] )
objDT = ObjectClrNew( "System.DateTime", objYear, objMonth, objDay, objHour, objMin, objSec )
;convert to UTC
strDate = objDT.ToLongDateString
strDate = strDate:@CRLF:objDT.ToLongTimeString
strDate = date:@CRLF:objDT.ToUniversalTime:"(UTC)"
Message("",strDate)

[ChuckC]

Please note that NTFS and ReFS both store all timestamp values as UTC, and it is only the display of those timestamps that gets adjusted to a local time based on the time zone setting.

FAT, FAT16 and FAT32 all store timestamp values as local times.

[stanl]

Please note that NTFS and ReFS both store all timestamp values as UTC, and it is only the display of those timestamps that gets adjusted to a local time based on the time zone setting.

FAT, FAT16 and FAT32 all store timestamp values as local times.

Understood.  I prefaced my replies on the basis of conversion Local=>Universal as I interpreted the OP was asking about that. Wasn't sure if he was talking about files received from SFTP [or some server] or if created locally.

[td]

Here is a link to a 13 year old article in the Tech Database that asks and answers a very similar question using a different approach:

https://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/DllCall~Information+NTFS~File~Time~Stamps.txt

[stanl]

Here is a link to 13 year old article in the Tech Database that asks and answers a very similar question using a different approach:

https://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/DllCall~Information+NTFS~File~Time~Stamps.txt

Great reason for Congress to get off their butt's and eliminate DST. Seems a lot safer than ruling on climate change.

[ChuckC]

Now that I no longer have a commute, I find myself curiously detached from the whole DST issue... Not having to deal with 5 to 6 weeks of the sun in my eyes while commuting east in the a.m. and west in the p.m. followed by a DST adjustment that puts the sun in my eyes again for another 5 to 6 weeks somewhat relieved the problem.  Of course, I can still carp about it in November as it totally wrecks the alarm clock setting for deer hunting season after we revert to standard time... having sunrise moved to an earlier time makes it necessary to wake up a full hour earlier just to get into the woods before sunrise.

[td]

All of the west coast states have passed laws to change to year around Daylight Saving Time but it requires an act of The US Congress to allow that to happen... Up here near latitude 48 N the northern horizon is aglow from light bending over the pole after about 11:00 PM in late spring and early summer. It is something I still  marvel at.

[bottomleypotts]

Thanks for the reply guys.

An IntControl function that changes the date returned for all files (0-default as we are today,1-remove any DST,2-UTC time) would be the dream, so that I don't have to do this manually for all files.

In the meantime I can create my own UDF using Stan's CLR code to get the difference and subtract myself from all dates.

Appreciate the help.

[stanl]

In the meantime I can create my own UDF using Stan's CLR code to get the difference and subtract myself from all dates.
Appreciate the help.

WB has a neat FileYmdHms() function. And to be fair: more Tony code than Stan code ;) 

Code (WINBATCH) Select Expand



;Convert FileTime to a DateTime Object, return UTC time
file = "C:\temp\testps.txt"  ;my test
If fileExist(file)
   date=FileYmdHms(file)
Endif
arrYMDHMS = Arrayize( date, ":" )
;Create CLR Objects
objYear = ObjectClrNew( "System.Int32", arrYMDHMS[0] )
objMonth = ObjectClrNew( "System.Int32", arrYMDHMS[1] )
objDay = ObjectClrNew( "System.Int32", arrYMDHMS[2] )
objHour = ObjectClrNew( "System.Int32", arrYMDHMS[3] )
objMin = ObjectClrNew( "System.Int32", arrYMDHMS[4] )
objSec = ObjectClrNew( "System.Int32", arrYMDHMS[5] )
objDT = ObjectClrNew( "System.DateTime", objYear, objMonth, objDay, objHour, objMin, objSec )
;convert to UTC
strDate = objDT.ToLongDateString
strDate = strDate:@CRLF:objDT.ToLongTimeString
strDate = date:@CRLF:TimeFormat(objDT.ToUniversalTime,"yyyy:MM:dd HH:mm:ss"):"(UTC)"
Message(file,strDate)
Exit


[td]

Thanks for the reply guys.

An IntControl function that changes the date returned for all files (0-default as we are today,1-remove any DST,2-UTC time) would be the dream, so that I don't have to do this manually for all files.

In the meantime I can create my own UDF using Stan's CLR code to get the difference and subtract myself from all dates.

Appreciate the help.

A simple UDF for time conversion.

Code (winbatch) Select Expand

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; TimeAdjustToUTC
;; Input - WIL YMDHMS local time
;;
;; Returns - WIL YMDHMS time converted to UTC.
::
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
#DefineFunction TimeAdjustToUTC(strLocalTime)
   ;; Formula for conversion is "UTC = local time + bias".
   nActiveBias =  RegQueryDword(@REGMACHINE,"SYSTEM\CurrentControlSet\Control\TimeZoneInformation[ActiveTimeBias]", 0)
   If nActiveBias >= 0
      Return TimeAdd(strLocalTime,"0000:00:00:00:":nActiveBias:":00")
   Else
      Return TimeSubtract(strLocalTime,"0000:00:00:00:":Abs(nActiveBias):":00")
#EndFunction

;; Test
Local = TimeYmdHms()
UTC = TimeAdjustToUTC(Local)

Pause(Local, UTC)

[bottomleypotts]

A simple UDF for time conversion.

This does not work for me. nActiveBias=4294966636 and TimeAdd just cannot handle the request.

[stanl]

Tony's UDF works for me - no bias intended ;D

[td]

A simple UDF for time conversion.

This does not work for me. nActiveBias=4294966636 and TimeAdd just cannot handle the request.

Since bias is stored in minutes, a bias of 4294966636 would mean you are 1,193,046.29 hours before Greenich mean time. The last time I check one rotation of the earth is divided into 24 hours. Me things you might have some kind of system registry problem.

[td]

Take that back... Try this version to account for the registry value being stored as a DWORD and being returned as a string.

Code (winbatch) Select Expand

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; TimeAdjustToUTC
;; Input - WIL YMDHMS local time
;;
;; Returns - WIL YMDHMS time converted to UTC.
::
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
#DefineFunction TimeAdjustToUTC(strLocalTime)
   ;; Formula for conversion is "UTC = local time + bias".
   nActiveBias =  Int(RegQueryDword(@REGMACHINE,"SYSTEM\CurrentControlSet\Control\TimeZoneInformation[ActiveTimeBias]", 0))
   If nActiveBias >= 0
      Return TimeAdd(strLocalTime,"0000:00:00:00:":nActiveBias:":00")
   Else
      Return TimeSubtract(strLocalTime,"0000:00:00:00:":Abs(nActiveBias):":00")
#EndFunction

;; Test
Local = TimeYmdHms()
UTC = TimeAdjustToUTC(Local)

Pause(Local, UTC)

[stanl]

Nice Tony. Believe the OP has clear direction to proceed.

[td]

I had forgotten that RegQueryDword returns a string.  WIL has some special rules for very large integer relational comparisions in strings. Using the Int functions causes the comparision to revert to a simple integer comparision.

[stanl]

This has been a stab from the past. Going over some old scripts and I had been messing with UTC in WB since 2008 or earlier. In 2008 I used this UDF

Code (WINBATCH) Select Expand



#definefunction UTCToLocal(_YmdDate)
   nTimeBias = RegQueryDword(@REGMACHINE, "System\CurrentControlSet\control\TimeZoneInformation[ActiveTimeBias]")
   if nTimeBias > 0 then return TimeSubtract(_YmdDate,'00:00:00:00:':nTimeBias:':00')
   else if nTimeBias < 0 then return TimeAdd(_YmdDate,'00:00:00:00:':Abs(nTimeBias):':00') 
   return _YmdDate
#endfunction



which didn't use Int() and left off the optional flag. In other scripts, I used "MSScriptControl.ScriptControl" , or ObjectClrNew("System.TimeZoneInfo") and methods .Now / .UTCNow -   


Really a fascinating subject.

[td]

As long as the bias is positive you don't need to use the Int function. It is when the bias is negative, i.e., you live on the Europian, Asian,Australian or African continents that you need to use the Int function. It still best to use Int for conversion to UTC bias even when you live in the western hemisphere.

[stanl]

As long as the bias is positive you don't need to use the Int function. It is when the bias is negative, i.e., you live on the Europian, Asian,Australian or African continents that you need to use the Int function. It still best to use Int for conversion to UTC bias even when you live in the western hemisphere.

If you run the code I suggested for bias as the initial response to this thread you should come up with -7 while here in Raleigh it is -4. Always made sense to me as going West of UTC would be a negative bias, i.e you are 4 hours behind, which would reverse your logic for TimeAdd()/TimeSubtract(). 


Totally a moot point, and not meaning to be argumentative.... but as I wrote I deal with UTC timestamp which reflect our servers in PST, so string ends in -7:00, which would change to -8:00 on Nov 6.  These a convert to EST DateTimes [Excel Dates] as people I report to would like to see times in EST instead of adding 3 hours in their head.


Excel does not natively or easily convert UTC Timezone strings, but Power Query [or Get and Transform] does the conversion to DateTime in current Timezone with the click of a button.


Since I won't be alive in 2038 when UTC faces it's Y2K issue.... this has been fun.

Code (WINBATCH) Select Expand



:getbias
activebias = RegQueryDWORD( @REGMACHINE,"SYSTEM\CurrentControlSet\Control\TimeZoneInformation[ActiveTimeBias]")
machinegmtoffset = int ( ( activebias / 60 ) * ( -1 ) )
Message("Active Bias",machinegmtoffset)


:getdaylight
activebias = RegQueryDWORD( @REGMACHINE,"SYSTEM\CurrentControlSet\Control\TimeZoneInformation[DaylightBias]")
machinegmtoffset = int ( ( activebias / 216000 )/3600 * ( -1 ) )
Message("Daylight Bias",machinegmtoffset)




[td]

MSFT states that its bias is always  "UTC = local time + bias" for both the registry and APIs that return a bias. This means the bias is always negative in the  Western hemisphere. The only reason this came up is that the WIL registry function returns a DWORD as a string instead of an integer.  It does this for the decimal format because DWORDs are unsigned. We will probably add an option to the RegQueryDWORD function that adds a signed-integer format to the HEX and decimal formats. This makes the return value intuitive to use in arithmetic and relational WIL expressions.

{edit}
Arrrrgh. Did it again.  The sentence "This means the bias is always negative in the  Western hemisphere." should be "This means the bias is always positive in the  Western hemisphere."

[bottomleypotts]

On the other side of the planet we still need the INT() function to wrap the REGQUERY() or we get garbage Stan  ;D