Math on Time values(hh:mm:ss) - This worries me...

  • Thread starter Thread starter Rhonda
  • Start date Start date
R

Rhonda

I'm experimenting with multile records each having a time duration value
like 00:06:23(hh:mm:ss) for example.

I'm trying to learn how to add values, ie: 00:03:12 + 00:05:20 = 00:08:32.
This seems simple - but not for me. Eventually I want to average my values,
OR, if Avg() doesn't work, I'll Add all values then divide by number of
records...
Any suggestions appreciated
Rhonda
 
The Date/Time datatype in Access is really only meant for timestamps:
complete dates and times, not durations. This is because of how the values
are stored internally: it's an 8 byte floating point number, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.

The best way to hand durations is to store them as some other data type. If,
for example, you want precision down to the second, store them in a Long
Integer as total seconds. You'll need to write your own functions to convert
from total seconds to hh:nn:ss format (and possibly the inverse of that),
but that shouldn't be that difficult to do.
 
Ok
I sat aside my stored Duration and am testing:
CLng(DateDiff("s",[TimeOn],[TimeOff])). It converts most of my Differences
to seconds accurately, but some are strangely off, like 00:07:58 converts to
418 seconds instead of 478, 00:09:42 to 922 seconds.

I am able to get the Average by: Totalling seconds, dividing by
Count[ofRecords], and converting back to "hh:mm:ss" format.
Thank You.
 
Correction, the 922 should be 522.

I found that the inaccurate values are all 60 seconds off as follows.
0:07:58 418 sec, 0:09:42 522 sec, 0:10:24 564 sec,
0:05:36 276 sec.
This seemed so promising.
Thank you.

Rhonda said:
Ok
I sat aside my stored Duration and am testing:
CLng(DateDiff("s",[TimeOn],[TimeOff])). It converts most of my Differences
to seconds accurately, but some are strangely off, like 00:07:58 converts to
418 seconds instead of 478, 00:09:42 to 922 seconds.

I am able to get the Average by: Totalling seconds, dividing by
Count[ofRecords], and converting back to "hh:mm:ss" format.
Thank You.

Douglas J. Steele said:
The Date/Time datatype in Access is really only meant for timestamps:
complete dates and times, not durations. This is because of how the values
are stored internally: it's an 8 byte floating point number, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.

The best way to hand durations is to store them as some other data type. If,
for example, you want precision down to the second, store them in a Long
Integer as total seconds. You'll need to write your own functions to convert
from total seconds to hh:nn:ss format (and possibly the inverse of that),
but that shouldn't be that difficult to do.
Click to expand...
Click to expand...
 
I found somthing at fault. My control source:
DateDiff("n",[TimeOn],[TimeOff])\60) &
Format(DateDiff("n",[TimeOn],[TimeOff]) Mod 60,"\:00") &
Format(DateDiff("s",[TimeOn],[TimeOff]) Mod 60,"\:00"

Is causing the error. For example. It resolves the DateDiff (8/2/2003
2:39:03 PM, 8/2/2003 2:46:01 PM) to 0:07:58 instead of 0:6:58. So my
Seconds are accurate. This is still only happening when the TimeOn seconds
is a number larger than the TimeOff seconds number.

I'll try converting my accurate seconds to hh:mm:ss and forgetting my
original DateDiff Duration control source.

Rhonda said:
Ok
I sat aside my stored Duration and am testing:
CLng(DateDiff("s",[TimeOn],[TimeOff])). It converts most of my Differences
to seconds accurately, but some are strangely off, like 00:07:58 converts to
418 seconds instead of 478, 00:09:42 to 922 seconds.

I am able to get the Average by: Totalling seconds, dividing by
Count[ofRecords], and converting back to "hh:mm:ss" format.
Thank You.

Douglas J. Steele said:
The Date/Time datatype in Access is really only meant for timestamps:
complete dates and times, not durations. This is because of how the values
are stored internally: it's an 8 byte floating point number, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day.

The best way to hand durations is to store them as some other data type. If,
for example, you want precision down to the second, store them in a Long
Integer as total seconds. You'll need to write your own functions to convert
from total seconds to hh:nn:ss format (and possibly the inverse of that),
but that shouldn't be that difficult to do.
Click to expand...
Click to expand...
 
Correction, the 922 should be 522.

I found that the inaccurate values are all 60 seconds off as follows.
0:07:58 418 sec, 0:09:42 522 sec, 0:10:24 564 sec,
0:05:36 276 sec.
This seemed so promising.
Click to expand...

Please post your code. DateDiff() in fact works accurately to the
second. I suspect you're inadvertantly calculating a difference other
than the difference you want to calculate!
 
Back
Top