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

[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

 

[Douglas J. Steele]

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.

 

[Rhonda]

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.

 

[Rhonda]

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.

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.

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.

 

[Rhonda]

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.

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.

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.

 

[John Vinson]

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.

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!

 

[Rhonda]

DateDiff("s",[Time_On],[Time_Off]) AS Seconds2
So far its workin well.