>>>Suppose you have 2 different datetime values t1 and t2.
>>>
>>>If you subtract them e.g. t2 - t1, you get the time difference in seconds. Actually it seems to be the FLOOR() of the difference i.e.
>>>
>>>- anything less than 1 second returns 0
>>>- anything less than 2 seconds, but greater to or equal to 1 second, returns 1
>>>- etc.
>>>
>>>My understanding is that as an 8-byte type, DateTime values are stored with considerable precision, of the order of about 10 milliseconds (?) Is there any way to calculate differences with more precision?
>>>
>>>I understand SECONDS() can be used to get more precision, but what I'm dealing with are DateTime values stored in tables.
>>
>>vfp datetime variables store ms.
>>But DATETIME() return seconds only.
>>
>>
>>dt0=DTOT(DATE())
>>dt1=DATETIME()
>>dt2=m.dt1+0.643
>>? 0.000+(dt2-dt1)
>>CREATE CURSOR AA (T1 T , T2 T )
>>INSERT INTO AA VALUES (dt1,dt2)
>>? 0.000+(t2-t1)
>>
>>
>>vfp SECONDS() is not reliable respect to the real time.
>>
>>then uses a system API to get datetime with ms info.
>
>That seems to be the key, that DATETIME( ) does not return the milliseconds portion. So, if values were recorded to data tables using just DATETIME( ), the millisecond portions simply aren't there and can't be extracted.
>
>I've done some experimenting with ways to restore sub-second precision:
>
>1. Use the GetTickCount( ) / GetTickCount64( ) API calls as you suggest
>2. Add the fractional part of SECONDS( ) to any DATETIME( ) call i.e.
>
>* instead of just calling DATETIME( ), use
>ltDateTime = DATETIME( ) + MOD( SECONDS( ), 1 )
>
>Both seem to work reasonably well. Here's some test code:
>
>LPARAMETERS ;
> tlUseTickCount
>
>* Parameters:
>* .T. to run the test using the GetTickCount( ) API call
>* .F. to run the test appending MOD( SECONDS( ), 1 ) to DATETIME( ) calls
>
>* Takes about 25 seconds to run with loop count and maximum sleep time as below
>
>LOCAL ;
> ltStart ;
> , ltEnd ;
> , lnIx ;
> , lnmSecSleep ;
> , lnStart ;
> , lnEnd ;
> , lnmSecSleepMax
>
>m.lnmSecSleepMax = 500
>
>* mSecSleep = milliseconds execution pauses using Sleep API call
>* mSecDiff = milliseconds time difference between "start" and "end" times
>* in a perfect world, that would be exactly equal to mSecSleep
>* mSecOHead = milliseconds overhead, the amount of time that the difference
>* between the "start" and "end" times exceeds the Sleep time
>CREATE CURSOR Test ;
> ( mSecSleep I ;
> , mSecDiff I ;
> , mSecOHead I )
>
>DECLARE Sleep IN kernel32 INTEGER dwMilliseconds
>DECLARE INTEGER GetTickCount IN kernel32
>
>=RAND( -1 )
>
>FOR m.lnIx = 1 TO 100 STEP 1
> * Set a random sleep duration:
> m.lnmSecSleep = INT( RAND( ) * m.lnmSecSleepMax )
>
> IF m.tlUseTickCount
> m.lnStart = GetTickCount( )
> =Sleep( m.lnmSecSleep )
> m.lnEnd = GetTickCount( )
>
> INSERT INTO Test ;
> ( mSecSleep ;
> , mSecDiff ;
> , mSecOHead ) ;
> VALUES ;
> ( m.lnmSecSleep ;
> , m.lnEnd - m.lnStart ;
> , ( m.lnEnd - m.lnStart ) - m.lnmSecSleep )
>
> ELSE
> m.ltStart = DATETIME( ) + MOD( SECONDS( ), 1 )
> =Sleep( m.lnmSecSleep )
> m.ltEnd = DATETIME( ) + MOD( SECONDS( ), 1 )
>
> INSERT INTO Test ;
> ( mSecSleep ;
> , mSecDiff ;
> , mSecOHead ) ;
> VALUES ;
> ( m.lnmSecSleep ;
> , 1000 * ( m.ltEnd - m.ltStart ) ;
> , ( 1000 * ( m.ltEnd - m.ltStart ) ) - m.lnmSecSleep )
>
> ENDIF
>
>ENDFOR
>
>BROWSE NOWAIT
>
>I think I like the SECONDS( ) approach better. With that the results can be stored in a regular DateTime column.
>
>GetTickCount( ) counts milliseconds since system startup. It is an unsigned 32-bit integer and rolls over (back to zero) after about 49.7 days. That complicates time difference calculations. The value can't be stored in a VFP integer column because that is signed and only stores up to 2^31, so it would overflow in about 25 days. So the result would have to be stored in a currency column.
>
>On Vista and later GetTickCount64( ) is available, which effectively doesn't have the rollover problem, but that's not available for XP/Server 2003 etc.
It is not good to mix datetime() with seconds(), because you get the two values in two different times
( and on this case 1 ns is relevant ! )
run this some time ... and you get a random time glitch
CLEAR
FOR k=1 TO 1000000
IF INT(SECONDS())#DATETIME()-DTOT(DATE())
? SECONDS(),DATETIME(),DATETIME()-DTOT(DATE())
ENDIF
NEXT
