Summing Times, or Date/Time Data: An Instant or a Duration?

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:00

What 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;

See also: How to store, calculate, and compare Date/Time data in Microsoft Access , More Date and Time Topics on MyMSAccessBlog.