Storing Time Duration

[algecan]

Hi all,

I've done some research into this but can't really find a definitive answer. This questions spans the Table topic and Queries Topic so I'm hoping I can get an answer here.

I have a database where I need to store a time duration. I understand it's not best practice to store duration as a date/time field type. So from this I presume it would be best stored as a number (long integer?). Just to clarify I don't need to store the start or end time, I know I could easily use datediff to get the duration if doing it this way. I just need to store the duration as determined and entered by the user. For example if they enter 7.30, this would be 7 hours 30 minutes. Entering 5.15 would be 5 hours 15 minutes. I also don't want the user to enter the time as a decimal. Main reason for this is that it would be a pain for them to work out the decimal time of every duration they needed to enter.

The next thing I need to understand is the best way to calculate this. I need to sum all of these together to show the total duration spent. The important bit with this calculation is showing it in days where it is based on working hours in a day. For example our working day is 7hours 24 minutes (7.4 as a decimal). So if someone enters 8 hours as one duration, they have spent 1 day 36 mins working on a particular job. This obviously needs to be the same when summing these durations.

Can anyone offer any advice?

 

[JHB]

I can't see it should give any problem storing in a Date/Time field type:

SELECT Format(Sum([Atime]),"hh:nn:ss") AS Expr1
FROM Table17;

Oh yes - if it is more as 24:00 then it is start over again.

 

[plog]

You need to determine the smallest unit you will be using and save your durations in that unit in an integer field. From the examples you have given so far, it looks like minutes is that unit. So for 5 hours 15 minutes you would store 315 in your field, for 7 hours 30 minutes 450 minutes, etc.

As for inputing data, you can make a form for user to input time in any units they want (as long as its larger than your base unit), then calculate and store their input values as a sum of the base unit into your field. So you could make a form for users to put in days, hours, minutes, then when they click submit, you multiple the days field by 1440, the hours by 60 and then add the minutes and store that value.

For output, you can make a custom function that reverses the input function. Divide the total minutes by 1440, the remainder of that by 60, etc. and then the function returns a string that tells you how many days/hours/minutes which you can display on a report.

 

[The_Doc_Man]

plog's method (converting to smallest units as integers and writing your own formatting functions) works fine. I do not in any way wish to state otherwise. However, there is another way to "skin this cat" if you are interested...

A date/time field in Access is a CAST (a.k.a. TYPECAST) of a DOUBLE, for which the units are days and fractions of a day since the system reference date (sometime around 1-Jan-1900, give or take a day). The system time formatting routines (FormatDateTime, Format with a standard or custom date/time template) and the CDate() function do the necessary conversions to interchange between the internal date/time variable and a date/time string in any of dozens of formats. If you have the times in "conventional" format, with date and time segments, you manipulate the results in many ways.

If you have a date and time in text format, you can do CDate( string-holding-date-time ) to get the time in date/time format. This will convert to the internal format I described earlier. You cannot produce a negative date as a way to show dates in the 1800s. Note also that even if you were using the high-precision timer to get fractions of a second, you can store it in a date-time variable but the system formatting routines will truncate to the nearest second on output and will barf if you have fractions of a second on input.

If you have the time in date/time format, you can do CDbl( date-time-variable ) to get the time as a DOUBLE. The difference between two such DOUBLE variables is the elapsed time between them - in days and fractions based on wall-clock time, not in business shift time. I.e. if the start time is in the afternoon and time stops accumulating at, say, 5PM, and does not restart until 8 AM of the next day, that means you have to do a separate computation for that break in work shifts. That's on you to compute.

Now, the interesting part: If all you had was TIMES, both of which are on the same day, then my description above is STILL TRUE. I.e. the CDbl(CDate()) of a time string can be subtracted from the CDbl(CDate()) of a time string for the same day and the difference is STILL the elapsed time between them in days and fractions. (And if they are on the same day, shift changes are less of an issue and the difference will be 0 < x < 1.)

OK, so... now you've got elapsed times as doubles... how do you use them? Answer: Well, you can compute averages that will also be DOUBLE format for later conversion. You can add them to get total work-flow time (if you have more than one such elapsed time in a series). You can compute pay rates or charge-time value (based on DAILY rates, not hourly...).

You can do a Format( elapsed-time-variable, "hhh:mm:ss" ) to convert times to hours, minutes, and seconds. The elapsed time variable, if a date-time variable, can be used as-is. If it is a DOUBLE, then you can use CDate(double-format-elapsed-time) to do the reverse TYPECAST to get it back into conventional time format.

Please note that if you only use a format of "hh:mm:ss" (i.e., leave out the 3rd "h") then your time "wraps around" to another day if the elapsed time exceeds 24 hours and the Format routine will probably barf in that case.

Want to get that DOUBLE to hours? Use CLng( DOUBLE * 24 )
Want it in minutes? Try CLng( DOUBLE * 24 * 60 )
Want it in seconds? As you might have guessed, CLng( DOUBLE * 24 * 60 * 60 )
Oh, and of course if you want it as days, it is CLng( DOUBLE ).

Note that CLng truncates fractions! If the elapsed time for that last example is 1.99 days, you still get 1 day. On the other hand, if you wanted days and fractions of a day, such as "elapsed time is 1.99 days" then it is matter of just formatting the output for the raw DOUBLE variable to 2 decimal places.