how to get the microsecond portion of GetSystemTimePreciseAsFileTime/ " cLngLng of FileTime"

Question

Spring boarding off of Gustav's comments(Thanks, G) and another hour of searching, if someone can get me started on how to convert filetime to VBA LongLong, I'm confident I can handle it from there. There are examples of C++ FileTime two part variable to Quadword etc, but I know nothing about C++ so cannot mimic this in VBA.

Example in C++ is Stack Overflow question: FILETIME to __int64, scroll down to "Performing Arithmetic with File Times"

Essentially if I can get a custom cLngLng in VBA to handle FileTime input, I should be good to go.

Original Post:

I'm looking at accuracy of various time stamps in VBA, but hit a roadblock on using GetSystemTimePreciseAsFileTime. Apparently it gives a UTC type timestamp with a resolution of 100nSec in FileTime format. I cant figure out how to extract the last four decimal places of the seconds portion. If I use FileTimeToSystemTime it truncates the time to S.000 accuracy. I want to look at the rest of the GetSystemTimePreciseAsFileTime time stamp, i.e look at the seconds portion to the full S.0000000 resolution.

I was hoping I could extract the data and insert into Excel spreadsheet to let me figure this out..code works in saving the timing data, but I'm stuck. You can see the changes to FileTime.dwLowDate when you convert from file time to system time back to file time...is there a way to look at the changes to dwLowDate that tell you what the last four decimal places are?

Code so far is below. Plus of course the appropriate declarations and kernal32 subs and functions. Some of my reading suggests I might have to use "quadpart", whatever that is, as part of my calculations. Haven't been able to find out how to use quadpart or even if it exists in VBA.

Can someone steer me in the right direction to get the full resolution of the GetSystemTimePreciseAsFileTime?

Sub TestPrecisionTime()

Dim FileTimePercision As FileTime
Dim FileTimeBack As FileTime
Dim LocalSystemTime As SYSTEMTIME
Dim res As Long

GetSystemTimePreciseAsFileTime FileTimePercision  'Get the precision 100nSec system time
'convert it to System Time
res = FileTimeToSystemTime(lpFileTime:=FileTimePercision, lpSystemTime:=LocalSystemTime)
'Convert it back to FileTime to see what has changed from FileTimePercision.dwLowDateTime
res = SystemTimeToFileTime(lpSystemTime:=LocalSystemTime, lpFileTime:=FileTimeBack)

'Save the results so can try to develop algorithm to extract the last four digits of the time
'seconds down to 100 nsec, so S.0000000 "format", System time only gives S.000 "format"

Dim appAnalysis As Application
Dim wbPrecisionTime As Workbook
Dim wsPrecise As Worksheet
Dim sControlWB As String
If appAnalysis Is Nothing Then Set appAnalysis = Application
sControlWB = ActiveWorkbook.Name
If wbPrecisionTime Is Nothing Then Set wbPrecisionTime = appAnalysis.Workbooks(sControlWB)
If wsPrecise Is Nothing Then Set wsPrecise = wbPrecisionTime.Sheets("Precise")

With wsPrecise
            .Range("A2") = FileTimePercision.dwLowDateTime
            .Range("B2") = FileTimePercision.dwHighDateTime
            .Range("C2") = FileTimeBack.dwLowDateTime
            .Range("D2") = FileTimeBack.dwHighDateTime
            .Range("E2") = LocalSystemTime.wYear
            .Range("F2") = LocalSystemTime.wMonth
            .Range("G2") = LocalSystemTime.wDayOfWeek
            .Range("H2") = LocalSystemTime.wDay
            .Range("I2") = LocalSystemTime.wHour
            .Range("J2") = LocalSystemTime.wMinute
            .Range("K2") = LocalSystemTime.wSecond
            .Range("L2") = LocalSystemTime.wMilliseconds

End With
End Sub

Gustav · Answer 1 · 2021-08-14T10:04:10.457

To preserve the high resolution, you must use a FILETIME structure.

As you have found, that page mentions using FileTimeToSystemTime to obtain an "easy to display" value, but it forgets to tell, that this can only hold down to milliseconds.

So, I guess, you will have write your own function to convert/display the returned FILETIME value including microseconds. To hold the value, data type DateTime may not do as it is limited to milliseconds, though it can hold microseconds in a limited range (see VBA.Date):

    ' Interval with minimum one microsecond resolution.
    Public Const MaxMicrosecondDateValue    As Date = #5/18/1927#
    Public Const MinMicrosecondDateValue    As Date = #8/13/1872#

Better suited in VBA will be DateTime2. You could also convert to ticks and use data type BigInt.

Addendum:

This answer, Convert from a Windows filetime to a DateTime, explains the relation between ticks and the DateTime of .Net.

The functions DateDotNet and DotNet from module DateSpan shows how to convert between ticks and DateTime of VBA.

how to get the microsecond portion of GetSystemTimePreciseAsFileTime/ " cLngLng of FileTime"

1 Answers1