A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Storing time as integers



 
 
Thread Tools Display Modes
  #1  
Old June 3rd, 2010, 08:48 PM posted to microsoft.public.access.queries
SSi308
external usenet poster
 
Posts: 42
Default Storing time as integers

I have a call database that includes a field for length of call. I have
created a query that includes employee, date, and length of call.

I need to calculate the average call length per employee and as a whole. I
found one post that suggested the following:
"To store time intervals you should store them as integers that represent
the number of hours or minutes or seconds, etc.. and then you can perform
whatever math you like. The results can then be converted to the hh:nn:ss
format for display. "

How is this accomplished?

Lori
  #2  
Old June 3rd, 2010, 09:24 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Storing time as integers


You can do it with some math.

If you store the duration in seconds. And for example the duration is 7356

There is no field type in Access that stores duration.

One way to handle duration is to use a numeric field and store the number of
units of time that is the smallest that you are interested in recording. In
your case you might be storing the seconds. Then you can manipulate the
seconds to get duration in terms of hours, minutes and seconds. Assuming that
your field is named "Duration"

Seconds: Duration Mod 60
Minutes: (Duration\60) Mod 60
Hours: Duration\3600

To display that as hours minutes and seconds

Duration\3600 & ":" & Format((Duration\60) Mod 60,"00") & Format(Duration Mod
60,"00")


HTH


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

SSi308 wrote:
I have a call database that includes a field for length of call. I have
created a query that includes employee, date, and length of call.

I need to calculate the average call length per employee and as a whole. I
found one post that suggested the following:
"To store time intervals you should store them as integers that represent
the number of hours or minutes or seconds, etc.. and then you can perform
whatever math you like. The results can then be converted to the hh:nn:ss
format for display. "

How is this accomplished?

Lori

  #3  
Old June 3rd, 2010, 10:17 PM posted to microsoft.public.access.queries
SSi308
external usenet poster
 
Posts: 42
Default Storing time as integers

I understand the gist of it, but the field is currently a Date/Time field. It
is populated by importing call records from another program.

Can I write a query that will convert that field to seconds?

"John Spencer" wrote:


You can do it with some math.

If you store the duration in seconds. And for example the duration is 7356

There is no field type in Access that stores duration.

One way to handle duration is to use a numeric field and store the number of
units of time that is the smallest that you are interested in recording. In
your case you might be storing the seconds. Then you can manipulate the
seconds to get duration in terms of hours, minutes and seconds. Assuming that
your field is named "Duration"

Seconds: Duration Mod 60
Minutes: (Duration\60) Mod 60
Hours: Duration\3600

To display that as hours minutes and seconds

Duration\3600 & ":" & Format((Duration\60) Mod 60,"00") & Format(Duration Mod
60,"00")


HTH


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

SSi308 wrote:
I have a call database that includes a field for length of call. I have
created a query that includes employee, date, and length of call.

I need to calculate the average call length per employee and as a whole. I
found one post that suggested the following:
"To store time intervals you should store them as integers that represent
the number of hours or minutes or seconds, etc.. and then you can perform
whatever math you like. The results can then be converted to the hh:nn:ss
format for display. "

How is this accomplished?

Lori

.

  #4  
Old June 3rd, 2010, 10:24 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Storing time as integers

Lori:

While the DateTime data type in Access stores a point in time rather than a
duration, it can be used for the latter provided that each duration is less
than 24 hours. This is due to the fact that date/time values in Access are
implemented as a 64 bit floating point number, with the integer part
representing days and the fractional part the times of day. Access starts
counting from 31 December 1899 00:00:00, which is implemented as zero.

If you do maths on date/time values you get some strange results if you
format the result as date/time because what you are really seeing is the
length of time after 31 December 1899 00:00:00. If the result is formatted
as a number, however, what you see is the length of time in days. To
illustrate this 2.123 days if formatted as date/time is:

1 January 1900 02:57:07

If you need to store durations of more than 24 hours you have little choice
but to store them as numbers, or better still as separate fields for days,
hours, minutes and seconds (with DefaultValue properties of zero for each) as
this makes data entry intuitive. The four values can then be processed into
a single value of seconds and mathematical operations done on that value,
then converting the result back into its four constituents units for output.

However, for durations of less than 24 hours, which I'd guess your calls will
be, you can quite happily use a date/time data type. When you enter the
duration what you are really entering is a date/time on 31 December 1899, but
that is not relevant as the underlying implementation is a number of zero
point something, and it's this number on which arithmetical operations will
be undertaken under the skin. So you can average a set of date/time values
where each is less than 24 hours and the result will again be a number of
zero point something. It's very easy to display this as a time by adding the
following function to the database:

Public Function TimeToString(dtmTime As Date, _
Optional blnShowdays As Boolean = False) As String

Dim lngDays As Long
Dim strDays As String
Dim strHours As String

' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")

If blnShowdays Then
TimeToString = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
Else
TimeToString = Format((Val(strDays) * 24) + Val(strHours), "00") & _
Format(dtmTime, ":nn:ss")
End If

End Function

So in a query for instance you could have a computed column:

AverageCall: TimeToString(Avg([CallDuration]))

or you can do similarly in a computed control in a footer in a report:

=TimeToString(Avg([CallDuration]))

You'll have noticed that the function includes an optional blnShowdays
argument. If you omit this any result of 24 hours or more will show as hours:
minutes:seconds. But if you pass True into the function as this argument it
will return days:hours:minutes:seconds. This might be done when summing the
times over a long period to get the total calls duration, e.g.

=TimeToString(Sum([CallDuration], True))

Mostly you'd omit the optional argument and return the result in hours:
minutes:seconds, e.g.

53:36:58

but you do have the option to return it as:

2:05:36:58

if you wish.

Ken Sheridan
Stafford, England

SSi308 wrote:
I have a call database that includes a field for length of call. I have
created a query that includes employee, date, and length of call.

I need to calculate the average call length per employee and as a whole. I
found one post that suggested the following:
"To store time intervals you should store them as integers that represent
the number of hours or minutes or seconds, etc.. and then you can perform
whatever math you like. The results can then be converted to the hh:nn:ss
format for display. "

How is this accomplished?

Lori


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201006/1

  #5  
Old June 3rd, 2010, 10:40 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Storing time as integers

Why not? All you need to realize is that date/times are stored as
doubles, with the time portion being the decimal portion. So 12 hours
would be stored as .5 and 23:59.9999 seconds would be stored as .999....
To convert a time value to seconds, just multiply it by (24*60*60)


SSi308 wrote:
I understand the gist of it, but the field is currently a Date/Time
field. It is populated by importing call records from another program.

Can I write a query that will convert that field to seconds?

"John Spencer" wrote:


You can do it with some math.

If you store the duration in seconds. And for example the duration
is 7356

There is no field type in Access that stores duration.

One way to handle duration is to use a numeric field and store the
number of units of time that is the smallest that you are interested
in recording. In your case you might be storing the seconds. Then
you can manipulate the seconds to get duration in terms of hours,
minutes and seconds. Assuming that your field is named "Duration"

Seconds: Duration Mod 60
Minutes: (Duration\60) Mod 60
Hours: Duration\3600

To display that as hours minutes and seconds

Duration\3600 & ":" & Format((Duration\60) Mod 60,"00") &
Format(Duration Mod 60,"00")


HTH


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

SSi308 wrote:
I have a call database that includes a field for length of call. I
have created a query that includes employee, date, and length of
call.

I need to calculate the average call length per employee and as a
whole. I found one post that suggested the following:
"To store time intervals you should store them as integers that
represent the number of hours or minutes or seconds, etc.. and then
you can perform whatever math you like. The results can then be
converted to the hh:nn:ss format for display. "

How is this accomplished?

Lori

.


--
HTH,
Bob Barrows


  #6  
Old June 4th, 2010, 01:19 PM posted to microsoft.public.access.queries
SSi308
external usenet poster
 
Posts: 42
Default Storing time as integers

Thanks for the reply Ken.
If I add the function to the database will that affect all DateTime fields?
In addition to the LengthOfCall field there is a DateOfCall and TimeOfDay
field. Many of my reports are based on these two fields.
By adding the function will I be "breaking" anything?

Lori

"KenSheridan via AccessMonster.com" wrote:

Lori:

While the DateTime data type in Access stores a point in time rather than a
duration, it can be used for the latter provided that each duration is less
than 24 hours. This is due to the fact that date/time values in Access are
implemented as a 64 bit floating point number, with the integer part
representing days and the fractional part the times of day. Access starts
counting from 31 December 1899 00:00:00, which is implemented as zero.

If you do maths on date/time values you get some strange results if you
format the result as date/time because what you are really seeing is the
length of time after 31 December 1899 00:00:00. If the result is formatted
as a number, however, what you see is the length of time in days. To
illustrate this 2.123 days if formatted as date/time is:

1 January 1900 02:57:07

If you need to store durations of more than 24 hours you have little choice
but to store them as numbers, or better still as separate fields for days,
hours, minutes and seconds (with DefaultValue properties of zero for each) as
this makes data entry intuitive. The four values can then be processed into
a single value of seconds and mathematical operations done on that value,
then converting the result back into its four constituents units for output.

However, for durations of less than 24 hours, which I'd guess your calls will
be, you can quite happily use a date/time data type. When you enter the
duration what you are really entering is a date/time on 31 December 1899, but
that is not relevant as the underlying implementation is a number of zero
point something, and it's this number on which arithmetical operations will
be undertaken under the skin. So you can average a set of date/time values
where each is less than 24 hours and the result will again be a number of
zero point something. It's very easy to display this as a time by adding the
following function to the database:

Public Function TimeToString(dtmTime As Date, _
Optional blnShowdays As Boolean = False) As String

Dim lngDays As Long
Dim strDays As String
Dim strHours As String

' get whole days
lngDays = Int(dtmTime)
strDays = CStr(lngDays)
' get hours
strHours = Format(dtmTime, "hh")

If blnShowdays Then
TimeToString = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
Else
TimeToString = Format((Val(strDays) * 24) + Val(strHours), "00") & _
Format(dtmTime, ":nn:ss")
End If

End Function

So in a query for instance you could have a computed column:

AverageCall: TimeToString(Avg([CallDuration]))

or you can do similarly in a computed control in a footer in a report:

=TimeToString(Avg([CallDuration]))

You'll have noticed that the function includes an optional blnShowdays
argument. If you omit this any result of 24 hours or more will show as hours:
minutes:seconds. But if you pass True into the function as this argument it
will return days:hours:minutes:seconds. This might be done when summing the
times over a long period to get the total calls duration, e.g.

=TimeToString(Sum([CallDuration], True))

Mostly you'd omit the optional argument and return the result in hours:
minutes:seconds, e.g.

53:36:58

but you do have the option to return it as:

2:05:36:58

if you wish.

Ken Sheridan
Stafford, England

SSi308 wrote:
I have a call database that includes a field for length of call. I have
created a query that includes employee, date, and length of call.

I need to calculate the average call length per employee and as a whole. I
found one post that suggested the following:
"To store time intervals you should store them as integers that represent
the number of hours or minutes or seconds, etc.. and then you can perform
whatever math you like. The results can then be converted to the hh:nn:ss
format for display. "

How is this accomplished?

Lori


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201006/1

.

  #7  
Old June 4th, 2010, 04:09 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Storing time as integers

Lori:

Rest assured, adding the function to the database won't change any data at
all. All it does is reformat a value as hours;minutes:seconds when it's
called in a query or report. Calling it with the average of the LengthOfCall
fields in a query with TimeToString(Avg([LengthOfCall])) won't affect any of
the other columns or change any of the underlying values in the LengthOfCall
column, it will merely show the average as a time duration of hours;minutes:
seconds rather than as a decimal number of days, which is what you'd get if
you simply averaged the LengthOfCall values without calling the function.

BTW if you paste the function into a new module be sure to save the module
with a different name from that of the function; call it something like
basDateTimeStuff for instance, but the choice of a name for the module is
entirely yours.

Ken Sheridan
Stafford, England

SSi308 wrote:
Thanks for the reply Ken.
If I add the function to the database will that affect all DateTime fields?
In addition to the LengthOfCall field there is a DateOfCall and TimeOfDay
field. Many of my reports are based on these two fields.
By adding the function will I be "breaking" anything?

Lori

Lori:

[quoted text clipped - 95 lines]

Lori


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201006/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.