Time storage inaccuracy ?

  • Thread starter Thread starter Martin Dashper
  • Start date Start date
M

Martin Dashper

I am comparing the value of a time stored in a dateTime field in a
table with a hard coded value.
The table value is, nominally, 09:00:00. However when making the
query,

Set myRec = db.OpenRecordset("SELECT * FROM myTable WHERE myTime =
#09:00:00#")

the matching record is not found.

However,

Set myRec = db.OpenRecordset("SELECT * FROM myTable WHERE myTime >
#08:59:59# AND myTime < #09:00:00#")

will return the appropriate record.

Is there a workaround for this apparent anomaly.?

Martin Dashper
 
What about parsing the time into the time parts and testing the Hour part
AND the minutes part?
Terry
 
Martin Dashper said:
I am comparing the value of a time stored in a dateTime field in a
table with a hard coded value.
The table value is, nominally, 09:00:00. However when making the
query,

Set myRec = db.OpenRecordset("SELECT * FROM myTable WHERE myTime =
#09:00:00#")

the matching record is not found.

However,

Set myRec = db.OpenRecordset("SELECT * FROM myTable WHERE myTime >
#08:59:59# AND myTime < #09:00:00#")

will return the appropriate record.

Is there a workaround for this apparent anomaly.?
Click to expand...

How is the time value getting into the field? I just tested with a
table, into which I entered #9:00:00# into a DateTime field. Then I
selected using

SELECT tblData.* FROM tblData
WHERE tblData.DateField=#9:00:00#;

and that record was returned. How sure are you that the time value in
the field is exactly 9:00:00 AM?
 
How is the time value getting into the field? I just tested with a
table, into which I entered #9:00:00# into a DateTime field. Then I
selected using

SELECT tblData.* FROM tblData
WHERE tblData.DateField=#9:00:00#;

and that record was returned. How sure are you that the time value in
the field is exactly 9:00:00 AM?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
How sure can you be?
If I look at the table,I can see 09:00:00 and if I add the line

msgBox myRec!myTime

after the query, it displays - you guessed it - 09:00:00

Martin
 
Martin Dashper said:
How sure can you be?
If I look at the table,I can see 09:00:00 and if I add the line

msgBox myRec!myTime

after the query, it displays - you guessed it - 09:00:00
Click to expand...

My point was that, since date/time values are stored internally as
floating point numbers representing days and fractions of days relative
to a fixed "zero" date/time, there are two factors at work: (1)
floating-point imprecision, and (2) the fact that a date/time variable
or field can contain values that cannot be precisely rendered as hours,
minutes, and seconds. For example,

?cdbl(#9:00:00#)
0.375
?cdbl(#8:59:59#)
0.374988425925926
?CDate(0.375)
9:00:00 AM
?CDate( 0.374988425925926)
8:59:59 AM
?CDate( 0.374998)
9:00:00 AM
?CDate( 0.374998) = CDate(3.75)
False

If a time is manually entered as #09:00:00#, I believe (though I could
be wrong) that it will always be stored internally as the Double value
0.375, and thus will always compare as equal to #09:00:00#. However, if
a time is entered as a result of a function, such as Time(), it seems to
me that the stored value may display as #09:00:00#, but actually be some
value slightly less than or greater than 0.375. That's why I asked how
the time value was getting into the field.

I do agree that, if you're going to do reliable time comparisons down to
the second, you need to specify something other than exact equality,
unless the time values being compared were all entered manually. It's
probably more efficient to compare for a range; e.g.,

(MyTime > #08:59:59#) And ((MyTime < #09:00:01#)

than to call both the Hour and Minute functions for the comparison.
Also, if the field is indexed, the index can be used when you use a
range, but not when you call the functions.
 
Perhaps another technique would be to effectively round the values to the
nearest second using a conversion
?Format(time1, "hh:nn:ss") = Format(time2, "hh:nn:ss")

where time1 is the first time value to compare and time2 is the second time
value.
 
SFatz said:
Perhaps another technique would be to effectively round the values to
the nearest second using a conversion
?Format(time1, "hh:nn:ss") = Format(time2, "hh:nn:ss")

where time1 is the first time value to compare and time2 is the
second time value.
Click to expand...

Yes, but as I said before, doing it that way will prevent the use of
indexes in applying the query criterion.
 
Dirk said:
Yes, but as I said before, doing it that way will prevent the use of
indexes in applying the query criterion.
Click to expand...

Just to add to Dirk's response...

Assuming that a field in a table is indexed, this is the most efficient...
SELECT * FROM TableName WHERE FieldName = A_Value

This is almost as efficient...
SELECT * FROM TableName WHERE FieldName = SomeExpression

As is this...
SELECT * FROM TableName WHERE FieldName = SomeReallyComplicatedExpression

This however is NOT...
SELECT * FROM TableName WHERE ExpressionUsingAFieldName = A_Value

As soon as the operand on the left side of the equals sign is not a field
name (and only a field name) you have just rendered your index useless and
the query engine will have to scan every row in the table to evaluate that
expression.

So while using an expression or function on the left side of the equals sign
is often the simplest way to write a query it should be avoided except for
relatively small tables or in One-Off queries that are not used as part of
the production app.
 
Which is why I have moved away from timestamps for identifying added records.
I now prefer to use batch numbers which allow accurate and quick
identification of the record or groups of records
Terry
 
Back
Top