Storing time as integers
in [Queries]
|
Prev: querying unrelated tables
Next: Maybe Update Query?
From: SSi308 on 3 Jun 2010 15:48 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
From: John Spencer on 3 Jun 2010 16:24 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
From: SSi308 on 3 Jun 2010 17:17 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 > . >
From: KenSheridan via AccessMonster.com on 3 Jun 2010 17:24 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/Forums.aspx/access-queries/201006/1
From: Bob Barrows on 3 Jun 2010 17:40
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 |