Fractions of Seconds?

[NeilTerry]

Is there any way to get Access to recognize fractions of a second? I have a time variable of type date/time that I need to read from a text file (something along the lines of 22:12:04.322) but I can't seem to get Access to convert it correctly to a time for me. I've specified the format for the time as hh:mm:ss".000" but I get a conversion error every time I try to import from my file. Can anyone point me in the right direction?

thanks,

Neil

 

[Alexandre]

What is 04.322 in your example: 04 seconds and 344 1/1000th?
In an y case, a mask will not do. You will have probably to first import you data as text, then do some string processing to convert the values into a valid time format for access.

 

[The_Doc_Man]

Well, first the bad news. No format specifier in Access FORMAT function seems to support a fractional seconds format. However, the date/time variable has room for at least a few fractions. Based on some really quick-and-dirty computations, date/time variables have about 16 bits occupied in the day-number and 17 bits in the time of day accurate to seconds. (Actually, it doesn't come out even, but I said this was quick-and-dirty.)

A date/time variable is a double (real) that supports 64 bits, of which I think 56 are fractionals. So you have room for 23 bits, or about 0.000000126 seconds as the least-significant bit. Even if I did it wrong and you have 48 bits for fractions, you still have 15 bits, good enough for 0.0003 seconds and change...

You can actually treat a Date/Time like a Double, but the problem will always be to get the time and date extracted from it. Date is really no problem, even if you have added in the oddball fractions. The Format routine will do that part right. But to get back the fractions, you need to finagle it a little bit.

Remember this number well: 86400 seconds/day.

If you convert a date/time variable to a double variable (use CDbl to do it), you can strip out the day part. It is merely the number you get by doing a CLng of the double. Then convert that back to a double (this time having no fractional part). Subtract the day number from the Double. What is left is the fraction of a day since midnight.

If you multiply THAT by 86400, you get the number of SECONDS since midnight as another integer. Any fraction that is left is your fractions of a second.

If you understand this relationship, you can manually generate a VBA function that will handle this complex formatting function for you. (If you don't understand VBA, you might not be able to do this quite so easily.)

Now, having confused the issue, I personally would take this approach. I would use FORMAT function and the user-defined date/time formats to generate the date string. Then I would compute the number of seconds and convert that to hh:mm:ss format manually (because I want to avoid inherent rounding). Finally, I would use FORMAT on the remaining fraction with no leading 0, just a decimal point and some numbers.

When it was all done, I would concatenate it all together in the right order and proudly display my date/time as

dd-mmm-yyyy hh:nn:ss.fff

Be warned that if you let Access do the time formatting for you, there is a chance that seconds might be rounded up in some cases. Which is why I said I would do the time format myself.

 

[gsmisek]

The_Doc_Man wrote:

I would use FORMAT function and the user-defined date/time formats to generate the date string. Then I would compute the number of seconds and convert that to hh:mm:ss format manually (because I want to avoid inherent rounding).

and

Be warned that if you let Access do the time formatting for you, there is a chance that seconds might be rounded up in some cases. Which is why I said I would do the time format myself.

I know this is an old thread, but I thought I should point out that although The_Doc_Man correctly says that we need to account for the VBA Format function's rounding of date-time to the nearest second, he missed the fact that dates are also affected by this rounding.

If the time 11:59:59 PM is rounded up, then the date advances one day as well.

For example:

Format(CDbl(#12/31/2019 11:59:59 PM#) + 0.6 / 86400, "m/d/yyyy h:nn:ss AM/PM")

yields

1/1/2020 12:00:00 AM

Interestingly enough, a half second doesn't always round up to the next second. Not because banker's rounding is used, as in the VBA Round function, because it isn't, e.g., Round(2.5) vs. Format(2.5,"0")), but presumably because of all the approximate representations involved.

The example above doesn't round up if you use .5 second for .6 second:

Format(CDbl(#12/31/2019 11:59:59 PM#) + 0.5 / 86400, "m/d/yyyy h:nn:ss AM/PM")

yields

12/31/2019 11:59:59 PM

But swapping in 1988 works:

Format(CDbl(#12/31/1988 11:59:59 PM#) + 0.5 / 86400, "m/d/yyyy h:nn:ss AM/PM")

yields

1/1/1989 12:00:00 AM

--Greg

 

[Papa_Bear1]

Well, first the bad news. No format specifier in Access FORMAT function seems to support a fractional seconds format. However, the date/time variable has room for at least a few fractions. Based on some really quick-and-dirty computations, date/time variables have about 16 bits occupied in the day-number and 17 bits in the time of day accurate to seconds. (Actually, it doesn't come out even, but I said this was quick-and-dirty.)

A date/time variable is a double (real) that supports 64 bits, of which I think 56 are fractionals. So you have room for 23 bits, or about 0.000000126 seconds as the least-significant bit. Even if I did it wrong and you have 48 bits for fractions, you still have 15 bits, good enough for 0.0003 seconds and change...

You can actually treat a Date/Time like a Double, but the problem will always be to get the time and date extracted from it. Date is really no problem, even if you have added in the oddball fractions. The Format routine will do that part right. But to get back the fractions, you need to finagle it a little bit.

Remember this number well: 86400 seconds/day.

If you convert a date/time variable to a double variable (use CDbl to do it), you can strip out the day part. It is merely the number you get by doing a CLng of the double. Then convert that back to a double (this time having no fractional part). Subtract the day number from the Double. What is left is the fraction of a day since midnight.

If you multiply THAT by 86400, you get the number of SECONDS since midnight as another integer. Any fraction that is left is your fractions of a second.

If you understand this relationship, you can manually generate a VBA function that will handle this complex formatting function for you. (If you don't understand VBA, you might not be able to do this quite so easily.)

Now, having confused the issue, I personally would take this approach. I would use FORMAT function and the user-defined date/time formats to generate the date string. Then I would compute the number of seconds and convert that to hh:mm:ss format manually (because I want to avoid inherent rounding). Finally, I would use FORMAT on the remaining fraction with no leading 0, just a decimal point and some numbers.

When it was all done, I would concatenate it all together in the right order and proudly display my date/time as

dd-mmm-yyyy hh:nn:ss.fff

Be warned that if you let Access do the time formatting for you, there is a chance that seconds might be rounded up in some cases. Which is why I said I would do the time format myself.

I was happy to find this post - as I've run into a bit of a problem with sorting some items in an event log by date/time. It has three items in the table with the exact same date/time. In my estimation, this cannot be possible as the code is running and outputting these things along the way. It seems to me that fractions of a second MUST occur in doing this... but apparently not.

I thought maybe the display was showing the same date/time, but that it was properly storing the more precise date/time. After implementing this function - I now see that all three indeed have the exact same fractions of seconds as well. Yikes! I'm going to try a DoEvents (or maybe even a forced delay on certain entries) on my code that writes to the table to see if maybe it is doing a collect-and-dump and is somehow writing all three lines at the EXACT same INSTANT. That just seems impossible - but alas - this is what it is showing...

 

[The_Doc_Man]

Some time has passed since I first answered that one. Here is an update.

Problem #1 is that the FORMAT function absolutely stops DEAD at seconds. If you FORMAT a date field with time in it, you WILL NOT get below seconds in the output. This is a limitation on the FORMAT function and the code implied behind it. If you have ever looked at UTC-based time logs, you know that you CAN get fractions of a second out of windows. For more on that subject, look up NTP or Network Time Protocol.

Problem #2 is that Windows has more than one timer. The one they usually reference is only accurate to 1/60thof a second, or 16.6 msec. To get to more accuracy, you might need a high-precision timer.

High-Resolution Timers - Windows drivers

Starting with Windows 8.1, drivers can use the ExXxxTimer routines to manage high-resolution timers.

learn.microsoft.com

Note that you CAN get extra bits out of Windows timers because there IS a 64-bit crystal-controlled time-of-day clock somewhere in there, but getting access to it can be ... difficult. To start, that particular timer is an up-counter that ticks at 10 MHz, and it is an integer (LongLong) so tricky to manipulate if you have a 32-bit version of Access that doesn't support LongLong data type. Manipulating it in such a way as to avoid truncation is even tougher because VBA doesn't compile down to machine instructions. It compiles down to pseudo-code, so you don't REALLY know what the code is doing to your number except in broad-brush terms.

This article mentions timing for Windows. To get to the raw timers you need some API calls.

High Precision Event Timer - Wikipedia

en.wikipedia.org

For those interested in further discussion, may I suggest that you research "Windows High Precision Timer" to find many articles discussing the issue of ultra-precise timing on modern PCs. If you plan to pursue this, you WILL need to look up some API calls. Note also that prior to Win8, these features were not available and so if you are running something that old, it probably will not support what you want.

 

[isladogs]

I tested a variety of timers several years ago including the high resolution timer.
Unfortunately, though it is precise to at least 0.1 microseconds, it is less accurate than several other timers which have a theoretical precision of 1 millisecond. In reality most timers are subject to the system clock which typically update 64 times per second - approx. 16 milliseconds.
See my article

Timer Comparison Tests

This article compares 6 different timing methods with a view to determining the reliablility and consistency of each method.

www.isladogs.co.uk

 

[Papa_Bear1]

Some time has passed since I first answered that one. Here is an update.

Problem #1 is that the FORMAT function absolutely stops DEAD at seconds. If you FORMAT a date field with time in it, you WILL NOT get below seconds in the output. This is a limitation on the FORMAT function and the code implied behind it. If you have ever looked at UTC-based time logs, you know that you CAN get fractions of a second out of windows. For more on that subject, look up NTP or Network Time Protocol.

Problem #2 is that Windows has more than one timer. The one they usually reference is only accurate to 1/60thof a second, or 16.6 msec. To get to more accuracy, you might need a high-precision timer.

...

Wow - your answers are always spot on - and fully complete. Amazing...

I've found that, in this particular case, including a delay before one of the instances that writes out to the log was both effective and even necessary. Interestingly - inserting DoEvents in various places did not force it to separate the events - and I still had three events coinciding. Forcing a delay of a bit more than 1 second did the trick. (Turns out two of them actually make sense coinciding - as they are literally right next to each other in the code, but the other one --- not really --- as it is in an entirely separate routine.)

I just don't see how the time resolution inherent in Windows is insufficient to delineate between VBA lines of code running between two different routines. (If it really is faster than the 1/60th of a second as you pointed out --- then I guess 1 second just isn't quite enough to be sure!) I've been assuming VBA just isn't that fast since it is interpreting as it goes - but anyway. This quickly gets beyond my capabilities, and luckily, I don't need the precision - I just needed a distinction between two log entries, so I'll not be pursuing this further on this particular endeavor. I tend to take a "...do what it takes to make it work - and move on..." approach. I know that makes "real" developers cringe, or laugh, but anyway.

Many thanks @The_Doc_Man !!

 

[Papa_Bear1]

That looks like a very nice resource. If I get in such a bind in the future, I'll be looking at that!
thanks!

I tested a variety of timers several years ago including the high resolution timer.
Unfortunately, though it is precise to at least 0.1 microseconds, it is less accurate than several other timers which have a theoretical precision of 1 millisecond. In reality most timers are subject to the system clock which typically update 64 times per second - approx. 16 milliseconds.
See my article

 

[The_Doc_Man]

I tested a variety of timers several years ago including the high resolution timer.
Unfortunately, though it is precise to at least 0.1 microseconds, it is less accurate than several other timers which have a theoretical precision of 1 millisecond. In reality most timers are subject to the system clock which typically update 64 times per second - approx. 16 milliseconds.
See my article

Timer Comparison Tests

This article compares 6 different timing methods with a view to determining the reliablility and consistency of each method.

www.isladogs.co.uk

Are you sure about the "64"? Because if you look at the Timer function - which looks at the system clock - it is 60 ticks per second in the USA (but may be different in the EU, which uses 50 Hz power for some things). Long-term, USA 60 Hz power is very accurate though for short bursts there may be some minor variation. The power utilities adjust the frequency to gain or lost time until their long-term average interval is exactly 60 Hz. I repeat, I DO NOT make a claim for short-term frequency precision from the power utilities.

I just don't see how the time resolution inherent in Windows is insufficient to delineate between VBA lines of code running between two different routines.

Most modern machines are running a main system clock at between 1 GHz and 5 GHz, which governs memory maximum speed. Instructions for Intel x86-family machines are typically 2 memory cycles but some instructions have only 1 cycle and some have 3 cycles, with a few rare cases taking even more - like for the Longhorn CPU, IEEE X-floating which takes a LOT of cycles. A 2 GHz machine will average one instruction per nanosecond, which is 60 MILLION instructions per clock tick. Even the extended precision timer is typically only 10 MHz or 10 instructions on a 2 GHz machine. I've done emulations before and if you can't do a 60:1 emulation for simple math (60 hardware instructions for 1 emulated instruction), you shouldn't be programming for a living. So that 60 million hardware instructions should be at least 1 million emulations/second.

Colin (Isladogs) has done some loop timing studies that would give you an effective emulated-instructions-per-second rate that should be eye-opening regarding what Access is doing. But trust me, the ordinary Windows clock is too slow to distinguish between instructions in VBA.

 

[isladogs]

Are you sure about the "64"? Because if you look at the Timer function - which looks at the system clock - it is 60 ticks per second in the USA

No I'm not 100% certain whether its 60 or 64. I've seen both quoted in articles over the years
UK mains frequency is 50 Hz (averaged over time depending on load and again subject to fluctuation) but numerous tests I've done with various timers show intervals in apparent multiples of 0.016 seconds (but this is rounded). So the system clock is clearly not based on UK mains frequency.

 

[Papa_Bear1]

Colin (Isladogs) has done some loop timing studies that would give you an effective emulated-instructions-per-second rate that should be eye-opening regarding what Access is doing. But trust me, the ordinary Windows clock is too slow to distinguish between instructions in VBA.

I had no idea! Wow. OK - well - I guess my delay loops are merited then. Thanks!