Sum of Time

[Guest]

Hi.

I have a table that logs a user ID and the time they were logged into my
database, in hh:mm:ss format. I am trying to run a query to show me the
total time all users have been in logged in for;

e.g. Select Sum(Duration_Logged_In) etc, etc.....

The results comes back as a decimal number 14.11111 or similar.

If I format the result as a time, it gives me 02:14:36 (or similar). This
is way off what the total hours should be!

Can anyone advise me on how to do this?

Cheers,
Steve.

 

[Rick B]

TIME stored as hh:mm:ss is a set point in time. (It is two thirteen in the
afternoon). You can't use a time field to store a DURATION, which is a
length of time that something occurred (he was in the office for eight hours
and 13 minutes)

To store durations (and perform math on them) you need to store a numeric
value. In your example, you would store the total number of seconds. You
can then perform math on it. In your forms, reports, and queries, you can
format your number to display in the hh:mm:ss format if you'd like. There
have been many posts in the past on how to do this. Just search and read
previous posts on displaying a duration in the format of hours, minutes, and
seconds.

Hope that helps.

 

[Guest]

Date/Time fields store a point in time, not elapsed time. Elapsed time is a
quantity.
To return the value in the format you want, you will need to write an
expression that will perform the calculation. Here is an expression that
will do what you want.

format(datediff("s",st,et)\3600, "00:") & format((datediff("s",st,et) mod
3600)\60, "00:") & format((datediff("s",st,et) mod 3600) mod 60,"00")

In the above formula, st is Start Time and et is End Time.

 

[Guest]

Thanks for the replies.

I do not have 2 points in time to use the datediff function.

How do I convert my times from Time format to a useable format?

Thanks,
Steve.

 

[Guest]

First question is:
How is Duration_Logged_In calculated?

Since you want to show HH:MM:SS
it needs to be the total number of seconds the user was logged in.

If you can post the code where Duration_Logged_In is calculated, I can show
you how to modify the expression to get the correct calculation.

 

[Marshall Barton]

I have a table that logs a user ID and the time they were logged into my
database, in hh:mm:ss format. I am trying to run a query to show me the
total time all users have been in logged in for;

e.g. Select Sum(Duration_Logged_In) etc, etc.....

The results comes back as a decimal number 14.11111 or similar.

If I format the result as a time, it gives me 02:14:36 (or similar). This
is way off what the total hours should be!

The sum really should use a fundamental time unit (e.g.
seconds) instead of datetime values. A datetime value is a
point in time such as 1:24 AM and summing that doesn't
really make a lot of sense. Because we know how Access
stores datetime values, it can be done (i.e. the 14 in your
example is the number of days), but it is not a good idea to
rely on that kind of knowledge to get a calculaten to work.

Instead you should store the durations as the number of
seconds (calculated by DateDiff("s", starttime, endtime)).
Then, the total can be formatted by using a text box
expression like:
=total \ 3600 & Format((total \ 60) Mod 60, "\:00") &
Format(total Mod 60, "\:00)

 

[Guest]

Ok, my code to get the Duration is dteDuration =
CDate(Format(rstUser("Login_Date"), "hh:mm:ss")) - Time

I have broken down the time values stored in the Duration to total seconds.

How do I get access to display the seconds as a Time value again? I have
tried the obvious formats but I get 00:00:00.

Thanks,
Steve.

 

[Rick B]

I think you are missing the point. 02:05:06 is a set point in time. It
does not represent 2 hours, 5 minutes, and 6 seconds. You can't turn a set
point in time into a duration. You need to manually enter the number of
seconds into a NEW field.

If you want to try to figure the time out, you could create a formula to
take the time entry you have and pull the first two characters (which you
think represents a number of hours elapsed) and multiply it by 3600 (the
number of seconds in an hour). You could then take the fourth and fifth
digits and multiply them by 60, and then take the last two digits. You
could add the three results together, and then update a new number field
called "SecondsElapsed" or similar.

There is no way to take the current date/time field and easily convert it to
a duration. What is the duration for "two fifteen in the afternoon"?

Hope that helps.

 

[Guest]

I have not missed the point at all.

Thanks for everyones help, I have now converted my stored 'points in time'
to seconds and then back to a 'readable' HH:MM:SS format in a Sum query to
get my total logged in time for all users.

Thanks again,
Steve.

 

[Marshall Barton]

If I'm following all this correctly and if your Login_Date
field contains both the date and time parts (e.g. set to
Now), then your duration calculation should be:

dteDuration = DateDiff("s", Login_Date, Now)

That value and the sum of a column of those numbers can be
displayed in h:nn:ss style using the expression I posted
earlier.

Note that if the Login_Date field and the log out time do
not contain both the date and time parts, then your
calculations will fail (no matter how you do it) if the
login time and logout time cross the midnight boundary.