The question on the forum goes something like this:
I'm trying to add these times together but when it gets over 24 hours it goes back to zero instead of going to 25. What am I doing wrong?
The Access date/time data type is well suited to storing an instant in time. A date and/or time when something happened. It's not well suited to storing durations- how long something took. For that you're better to work with a number. It could be a number of seconds, minutes, hours or days: whatever's meaningful in your situation. And you'll have to decide what field size is appropriate as well. My message is: intervals or durations should be treated as numbers, not times.
Here are some example queries. First our sample data- start and stop times for productions runs on systems:
SystemID RunStartTime RunStopTime ======== ============ =========== 1 6:00:00 AM 2:00:00 PM 1 3:00:00 PM 11:00:00 PM 2 4:00:00 AM 6:00:00 AM 2 8:00:00 AM 11:00:00 AM 2 1:00:00 PM 2:00:00 PM 2 3:00:00 PM 11:00:00 PM
Here's where many people start with something like this- they subtract the times:
SELECT SystemID, RunStartTime, RunStopTime, [RunStopTime]-[RunStartTime] AS RunLength FROM tblRunTimes;If you tell Access to format the RunLength as time it works pretty well. The times for each run work OK.
With our sample data it even works to sum the times per system:
SELECT SystemID, Sum([RunStopTime]-[RunStartTime]) AS TotalRunLength FROM tblRunTimes GROUP BY SystemID;
But it falls apart when you sum the times for all the systems:
SELECT Sum([RunStopTime]-[RunStartTime]) AS TotalRunLength FROM tblRunTimes;gives you the following result:
TotalRunLength 6:00:00What happened? The total of all the run times is actually 30 hours. But Access thinks it's a date and time. So it's actually one day later at 6:00 in the morning.
The answer is to use DatePart() to calculate each run length in seconds:
SELECT SystemID, RunStartTime, RunStopTime, DateDiff("s",[RunStartTime],[RunStopTime]) AS RunLength FROM tblRunTimes;then when you sum it you get total run time in seconds per system:
SELECT SystemID, Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength FROM tblRunTimes GROUP BY tblRunTimes.SystemID;or for all systems:
SELECT Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength FROM tblRunTimes;
Treating the data as seconds will make your calculations work properly. You can sum, average, do stats, whatever you need. When you present information to the users you'll have to decide whether they want to see seconds or something else. To show hours instead simply divide by 3,600 (the number ofseconds in an hour). Here's an all SQL approach to break the total seconds down into days, hours, minutes and seconds:
SELECT Sum(DateDiff("s",[RunStartTime],[RunStopTime])) AS RunLength, Int([RunLength]/(60*60*24)) AS Days, Int(([RunLength]-([Days]*(60*60*24)))/(60*60)) AS Hours, Int(([RunLength]-[Days]*(60*60*24)-[Hours]*(60*60))/(60)) AS Minutes, Int([RunLength]-[Days]*(24*60*60)-[Hours]*(60*60)-[Minutes]*(60)) AS Seconds FROM tblRunTimes;