MS Access: SUM on Date/Time fields

[Zaur Bahramov]

Hi!

I have a table as follows:
tblCalls with fields
UserId Number
PhoneNumber Text
CallTime Date/Time
CallDuration Date/Time

Call duration is like this:
12:15:26
14:09:15
01:40:30
etc

When I do SUM on CallDuration I get 04.05.11 instead of let's say 28:05:11.

Is there a workaround for this?

 

[Stefan Hoffmann]

hi Zaur,

CallTime Date/Time
CallDuration Date/Time

A duration is a time span, while the data type Date/Time is a point in time.

When I do SUM on CallDuration I get 04.05.11 instead of let's say 28:05:11.

So save either the whole interval:

CallStart Date/Time
CallEnd Date/Time

or save the duration in a time resolution of your choice:

CallDurationSeconds Long

For the actual calculation of the time span take a look at DateDiff().


mfG
--> stefan <--

 

[Douglas J. Steele]

The Date/Time data type is intended for timestamps: dates and times. It's
really not appropriate for storing durations.

Determine the granularity you want (seconds? minutes?), store your times as
Long Integers where 1 represents the appropriate unit, and do your
arithmetic on the Long Integer field.

You might want to add functions that will convert from Long Integer to
hh:nn:ss and vice-versa. Something like:

Function LongToHHNNSS(Duration As Long) As String
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngHours = Duration \ 3600
lngMinutes = Duration Mod 3600
lngMinutes = lngMinutes \ 60
lngSeconds = Duration Mod 60

LongToHHNNSS = lngHours & ":" & _
Format(lngMinutes, "00") & ";" & _
Format(lngSeconds, "00")

End Function

Function HHNNSSToLong(Duration As String) As Long
' Expects input to have two colons.
' Will return -1 if it doesn't.
Dim varParts As Variant

varParts = Split(Duration, ":")
If UBound(varParts) = 2
HHNNSSToLong = varParts(0) * 3600 +
varParts(1) * 60 + varParts(2)
Else
HHNNSSToLong = -1
End If

End Function

 

[John Spencer]

You can get the total seconds and sum that. Then you can change that back to
a string in the format you want to display.

One way to get the total number of seconds
Hour(CallDuration) * 3600 + Minute(CallDuration)*60 + Second(CallDuration)

Another way
DateDiff("s",CallDuration,0)

Another way
CDbl(CallDuration) * 86400

Now that you have the number of seconds, you should be able to user Douglas
function to do the display you want.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Sheridan]

Date/time values are implemented in Access as a 64 bit floating point number,
with the integer part representing the days and the fractional part the times
of day. When you enter a time without a date you are in fact entering the
time on 30 December 1899, which is 'day-zero' in Access's date/time
implementation.

When you sum date/time values what you end up with is a floating point
number, which is the equivalent of another date/time value, not the sum of
the time durations. However, it is possible to return this as a string in
the format hh:nn:ss by adding the following function to a standard module in
the database:

Public Function TimeSum(dblTotalTime As Double) As String

Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String

'get number of hours
lngHours = Int(dblTotalTime) * HOURSINDAY + _
Format(dblTotalTime, "h")

' get minutes and seconds
strMinutesSeconds = Format(dblTotalTime, ":nn:ss")

TimeSum = lngHours & strMinutesSeconds

End Function

And then calling it in a query with:

TimeSum(Sum([CallDuration]))

While this works, it is nevertheless not best programming practice as it
does rely on the implementation of the date/time data type. Reliance on the
implementation is something best avoided, albeit not infrequently done. It
would be better to store the call duration either in a single column as a
number of seconds, or as separate columns CallHours, CallMinutes and
CallSeconds, each with a DefaultValue of zero and Nulls prohibited. Whether
you change to one of these approaches is for you to decide in the light of
how strictly you want to aim for best practice.

A practical consideration of course is what you'd currently do with a call
of more than 23:59:59 duration, which you can't enter as a value in a
date/time column. A call of that duration may be thought unlikely, but its
theoretically possible, and therefore Murphy's Law comes into play – if
something can go wrong, sooner or later it will!

Ken Sheridan
Stafford, England

 

[James A. Fortune]

The Date/Time data type is intended for timestamps: dates and times. It's
really not appropriate for storing durations.

Determine the granularity you want (seconds? minutes?), store your times as
Long Integers where 1 represents the appropriate unit, and do your
arithmetic on the Long Integer field.

You might want to add functions that will convert from Long Integer to
hh:nn:ss and vice-versa. Something like:

Function LongToHHNNSS(Duration As Long) As String
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long

lngHours = Duration \ 3600
lngMinutes = Duration Mod 3600
lngMinutes = lngMinutes \ 60
lngSeconds = Duration Mod 60

LongToHHNNSS = lngHours & ":" & _
Format(lngMinutes, "00") & ";" & _
Format(lngSeconds, "00")

End Function

Function HHNNSSToLong(Duration As String) As Long
' Expects input to have two colons.
' Will return -1 if it doesn't.
Dim varParts As Variant

varParts = Split(Duration, ":")
If UBound(varParts) = 2
HHNNSSToLong = varParts(0) * 3600 +
varParts(1) * 60 + varParts(2)
Else
HHNNSSToLong = -1
End If

End Function

The way I went about it was quite similar to what Douglas did (his using
the Split function was a nice idea):

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/be7e101c5176ebd8

Note: There's a problem with seconds stored in a Long variable when you
reach about 68 years duration.

I think that a solar year of 365.2422 days is stated in the "Oxford
Companion to the Year" by Bonnie
Blackburn and Leofranc Holford-Strevens. The leap year adjustment shows
that that value is a reasonable one:

365 + 1/4 - 1/100 + 1/400 = 365.2425 days

From Access Help:
Long data type -2,147,483,648 to 2,147,483,647

Unit Equation:

seconds {1 h / 3600 s} {1 d / 24 h} {1 y / 365.2422 d} = years

2147483647 seconds => 68.0511 years (Note: 0.0511 years is between 18
and 19 days)

James A. Fortune
(e-mail address removed)

 

[Ken Sheridan]

68 years of phone calls? Even my kids couldn't manage that; though they make
a good attempt!

Ken Sheridan
Stafford, England

 

[James A. Fortune]

68 years of phone calls? Even my kids couldn't manage that; though they make
a good attempt!

Ken Sheridan
Stafford, England

You never know where your code is going to end up. The times of
running and swimming races that prompted me to write that code to answer
some NG questions aren't likely to go past 68 years either, but the
warning is there just in case .

James A. Fortune
(e-mail address removed)