Using Time for hours of Usage

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that I put the login and logout time then calculate the
time spent.

I am having no problem with this operation, however when I try to add the
time spent to show how much total time a person or persons spent in the lab I
get funky numbers in my report, how can I show these numbers properly to be
all in hours:minutes even if it's over 24 hours
 
Mike,

Access computes times on the basis that 1 =1 day, the time format goes from
00:00 to 23:59, therefore if your total time spent is 25h-15m, you see 01:15
etc

If the total ti,e spent *never* exceeds 31 days you can change the format of
the total time spent to:
d:hh:nn

If it does or may exceeds 31 days, the simplest way to see more than 24
hours is to multiply the sum calculation by 24

=Sum([TimeSpent])*24
*and* set the format to a number format, 0.00 or similar

The result will be in decimal hours 5h:15m=1.25

Regards/JK
 
Int([YourTotal] * 24) & ":" &
Format( ([YourTotal] * 24 - Int([YourTotal] * 24)) * 60, "00")

should give the total in h:nn.
 
Access implements date/time values as 64 bit floating point numbers as an
offset from 30 December 1899 00:00:00. So if you enter a time without a date
you are really entering the time on 30 December 1899. Computing the duration
between two time values is not a problem if they are on the same day,
whatever that day might be, as the value when you subtract one from the other
is the floating point number representing another time on the day, e.g.

#15:00:00# - #11:30:00# = 0.145833333333333

If you format this as date/time:

Format(0.145833333333333, "dd mmmm yyyy hh:nn:ss")

this gives:

30 December 1899 03:30:00

If you format it just as the as time:

Format(0.145833333333333, " hh:nn:ss")

you get:

03:30:00

which is of course the duration between the two times as well as the point
of time on 30 December 1899 which the underlying value represents.

If you sum the durations computed in this way in different rows of a table
and the sum is more than 24 hours the result expressed as a time will not be
the total duration but a lower time duration. This is because what you are
seeing is in fact the time part of a date/time value on a day after 30
December 1899, e.g. 31 December 1899.

To express the underlying value as a time duration in the format
hours:minutes:seconds you can use a simple VBA function like this:

Public Function TimeDuration(dblDuration As Double) As String

Const HOURSINDAY = 24
Dim strDays As String
Dim strMinutesSeconds As String

'get number of days
strDays = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")

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

TimeDuration = strDays & strMinutesSeconds

End Function

Put the function in a standard module and pass the result of the summation
of the time durations into it to get the total duration formatted as
hours:minutes:seconds e.g. in a query

SELECT Employee,
TimeDuration(SUM(Logout – Login)) AS TimeInLab
GROUP BY Employee;

or you could call the function in a report, e.g. as the ControlSource of a
control in a group footer:

=TimeDuration(Sum([Logout] – [Login]))

Ken Sheridan
Stafford, England
 
MikeB said:
to show how much total time a person or persons spent in the lab I
get funky numbers in my report, how can I show these numbers properly to be
all in hours:minutes even if it's over 24 hours
Click to expand...

How did you do it? I'll wager you used arithmetic e.g.

2006-01-04T13:14:15 - 2006-01-01T03:02:01 = 1900-01-02T10:12:14

I'd suggested that is not an intuitive sum.

Now, in trying to be helpful some people will tell you about DATETIME
values really being represented using double float values, mention
epoch dates, tell you to multiply by 24, etc. But they won't tell you
that what you did wrong was to use arithmetic on DATETIME values.

The right answer is to operate on temporal data using temporal
functions. To find the interval between two DATETIME values, use the
DATEDIFF function. Its first argument is a string representing the
interval granularity (e.g. 'n' for minutes) and it returns a (long)
integer representing the number of granules. Formatting, say, 4932
minutes as hours and minutes should then be a trivial matter.

Jamie.

--
 
Jamie said:
How did you do it? I'll wager you used arithmetic e.g.

2006-01-04T13:14:15 - 2006-01-01T03:02:01 = 1900-01-02T10:12:14

I'd suggested that is not an intuitive sum.

Now, in trying to be helpful some people will tell you about DATETIME
values really being represented using double float values, mention
epoch dates, tell you to multiply by 24, etc. But they won't tell you
that what you did wrong was to use arithmetic on DATETIME values.

The right answer is to operate on temporal data using temporal
functions. To find the interval between two DATETIME values, use the
DATEDIFF function. Its first argument is a string representing the
interval granularity (e.g. 'n' for minutes) and it returns a (long)
integer representing the number of granules. Formatting, say, 4932
minutes as hours and minutes should then be a trivial matter.

Jamie.
Click to expand...

Jamie,

I agree with you totally on this point. I'd like your opinion on a
related issue. In:

http://groups.google.com/group/comp.databases.ms-access/msg/84c54d999e37bd14?hl=en&

I used CDate("00:00:00") as an origin for converting a time into
seconds. Do you see any problems with that or see a better way to do that?

Thanks,

James A. Fortune
(e-mail address removed)
 
Douglas said:
I'd be inclined to use TimeSerial(0,0,0) instead, but don't really have any
rational reason.
Click to expand...

I know what you mean. Casting a string as a DATETIME doesn't 'feel' as
natural as a DATETIME constructor function with numeric arguments. Not
much to choose between them, really.

Jamie.

--
 
Jamie said:
I know what you mean. Casting a string as a DATETIME doesn't 'feel' as
natural as a DATETIME constructor function with numeric arguments. Not
much to choose between them, really.

Jamie.
Click to expand...

I really like Douglas' idea. I'm being a bit lazy here, but does SQL
Server have a native TimeValue function? Also, what is the syntax for
casting to DATETIME in native SQL Server? I've always used an ADP or
VBScript when interfacing with SQL Server so I haven't had to find out
the answers to those questions. Thanks again to both of you for your input.

James A. Fortune
(e-mail address removed)
 
Not in T-SQL AFAIK. There is a mentioned of TimeSerial in MDA
(Multi-Dimensional Analysis) but I haven't touched that area yet.

I have been using CONVERT for this.
 
Van said:
Not in T-SQL AFAIK. There is a mentioned of TimeSerial in MDA
(Multi-Dimensional Analysis) but I haven't touched that area yet.

I have been using CONVERT for this.
Click to expand...

Thanks Van. I've been concentrating on making my Access SQL have more
of the look and feel of T-SQL lately and was looking to see if current
practices using SQL Server suggest a particular methodology.

James A. Fortune
(e-mail address removed)
 
James said:
what is the syntax for
casting to DATETIME in native SQL Server?
Click to expand...

I use a string (in ISO 8601 format of course) explicitly CAST as
DATETIME, avoiding the proprietary CONVERT as much as possible (but
often cannot be avoided when scrubbing data as in the OP's case) e.g.

SELECT CAST('2006-10-09T10:57:17.887' AS DATETIME)

You have to be careful with language issues in SQL Server: most
datetime formats should be considered unsafe. See:

http://www.karaszi.com/sqlserver/info_datetime.asp

The closest equivalent to the above in Access/Jet would be:

SELECT CDATE('2006-10-09 10:57:18')

but I tend to rely on Jet's DATETIME literal prefix/suffix character
e.g.

SELECT #2006-10-09 10:57:18#

Jamie.

--
 
Jamie said:
I use a string (in ISO 8601 format of course) explicitly CAST as
DATETIME, avoiding the proprietary CONVERT as much as possible (but
often cannot be avoided when scrubbing data as in the OP's case) e.g.

SELECT CAST('2006-10-09T10:57:17.887' AS DATETIME)

You have to be careful with language issues in SQL Server: most
datetime formats should be considered unsafe. See:

http://www.karaszi.com/sqlserver/info_datetime.asp

The closest equivalent to the above in Access/Jet would be:

SELECT CDATE('2006-10-09 10:57:18')

but I tend to rely on Jet's DATETIME literal prefix/suffix character
e.g.

SELECT #2006-10-09 10:57:18#

Jamie.
Click to expand...

I appreciate your comments Jamie. My problem is that your answer to
this question creates new questions for me to ponder. I want to avoid
specifying explicitly the base date used by the SQL implementation
whenever possible. I like the ISO idea. American dates seem wrong in
going from month to day to year -- definitely desultory and
non-intuitive. Most American users do not want to see the ISO format,
but they don't get to see what date format I use in queries. Perhaps
part of the solution for me is to write some user-defined functions in
Access that allow the SQL to look more like T-SQL. The ideal situation
is to write Access queries that involve the minimum number of changes
when converting to T-SQL. I realize that using Access as a front end
for SQL Server, if I desire, allows me to continue using an
Access-specific SQL style. The SQL used in Access has been great for
introducing me to SQL and relational database design concepts; it's just
too bad that Microsoft's great interest in standards didn't happen
sooner. My programming style has been to seek methods that are
universal and robust. The ISO date format, while not universal, is at
least international :-). These issues will require more time for me to
come up with a plan that minimizes the differences. Also, there is no
guarantee that the two versions of SQL will converge to standards or
even converge to each other.

James A. Fortune
(e-mail address removed)

Access Tip:

Instead of setting text field sizes to 255 and letting Access adjust its
own file size to the amount of storage used, get used to specifying
definite text field sizes and enforcing those limits on forms. That
way, if the data is converted to SQL Server, which reserves 255
characters if 255 is specified, you don't waste lots of storage on text
fields. Skip that idea if users get irate when text field size limits
are encountered :-).
 
James said:
I want to avoid
specifying explicitly the base date used by the SQL implementation
whenever possible.
Perhaps
part of the solution for me is to write some user-defined functions in
Access that allow the SQL to look more like T-SQL.
Click to expand...

In VBA you'd struggle to write a UDF that could be used like this:

SELECT CAST('2003-02-28' AS DATETIME);

You'll find it easier to write one to mimic the proprietary CONVERT :(
there is no
guarantee that the two versions of SQL will converge to standards or
even converge to each other.
Click to expand...

TSQL seems to be getting there but *very* slowly. Access? I can't see
it happening, myself. Jet 4.0 was a huge step in the right direction
but *that* development was carried out by the SQL Server team, who are
no longer at the helm. See:
http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx.
American dates seem wrong in
going from month to day to year -- definitely desultory and
non-intuitive.

Access Tip:

Instead of setting text field sizes to 255 and letting Access adjust its
own file size to the amount of storage used, get used to specifying
definite text field sizes and enforcing those limits on forms. That
way, if the data is converted to SQL Server, which reserves 255
characters if 255 is specified, you don't waste lots of storage on text
fields.
Click to expand...

That would appear to be a misstatement.

http://msdn2.microsoft.com/en-us/library/ms176089.aspx

"The storage size [for VARCHAR] is the actual length of data entered +
2 bytes"

Reminds me of the one on the Daily WTF: Tip of the Day: Did you
know...you're tips file is missing.

Anyhow, everyone knows the signature of the Access user is the
NVARCHAR(50) column <g>.

Jamie.

--
 
Jamie said:
In VBA you'd struggle to write a UDF that could be used like this:

SELECT CAST('2003-02-28' AS DATETIME);
Click to expand...

That's exactly what I had in mind.
You'll find it easier to write one to mimic the proprietary CONVERT :(




TSQL seems to be getting there but *very* slowly. Access? I can't see
it happening, myself. Jet 4.0 was a huge step in the right direction
but *that* development was carried out by the SQL Server team, who are
no longer at the helm. See:
http://blogs.msdn.com/access/archive/2005/10/13/480870.aspx.




Careful now <g> or you'll be asked to account for the
spelling/pronunciation of 'lieutenant' on this side of the pond.
Click to expand...

Either way, I AM on this side of the pond :-).
Access Tip:

Instead of setting text field sizes to 255 and letting Access adjust its
own file size to the amount of storage used, get used to specifying
definite text field sizes and enforcing those limits on forms. That
way, if the data is converted to SQL Server, which reserves 255
characters if 255 is specified, you don't waste lots of storage on text
fields.
Click to expand...


That would appear to be a misstatement.

http://msdn2.microsoft.com/en-us/library/ms176089.aspx

"The storage size [for VARCHAR] is the actual length of data entered +
2 bytes"

Reminds me of the one on the Daily WTF: Tip of the Day: Did you
know...you're tips file is missing.

Anyhow, everyone knows the signature of the Access user is the
NVARCHAR(50) column <g>.
Click to expand...

Thanks for the correction. I'll assume I would not have chosen the
wrong field type, so it was a misstatement. Does this suggest that the
max size should always be chosen for text fields? I'm still in the
nascent stages with T-SQL but I'm trying to learn quickly.

James A. Fortune
(e-mail address removed)

Britain is a small island that inspires fierce patriotism from its
citizens. -- Dr. Helen Williamson nee Downing
 
James said:
"The storage size [for VARCHAR] is the actual length of data entered +
2 bytes"
Click to expand...

Does this suggest that the
max size should always be chosen for text fields?
Click to expand...

Definitely not. Rather, what it is implying is that if the text length
varies but not by much e.g.

control_status VARCHAR(8) DEFAULT 'Enabled' NOT NULL
CHECK control_status IN ('Enabled', 'Disabled')

it might be better to use CHAR(n).

Note that CHAR(n) is available in Jet 4.0 but only in ANSI-92 query
mode (or equivalent e.g. ADO) so it doesn't seem to be widely used at
present (the same applies to VBA's String * n type but for different
reasons, of course).

....or should I say NCHAR(n), because Jet text columns are Unicode by
default. To get an ASCII *equivalent* you need to use e.g. (ANSI-92
query mode only):

control_status CHAR(8) WITH COMPRESSION DEFAULT 'Enabled' NOT NULL

however, such a column still appears in the Information Schema as a
"null-terminated Unicode character string" so perhaps I should remove
the word "by default" from my earlier statement :(

I fudged it, should have been

SELECT CAST('2003-02-28T00:00:00' AS DATETIME);

because '2003-02-28' is an unsafe format in SQL Server (but not in
Jet). Anyhow, I don't see how you can achieve the above syntax in VBA.

Jamie.

--



Jamie.

--
 
Jamie said:
James said:
"The storage size [for VARCHAR] is the actual length of data entered +
2 bytes"
Click to expand...

Does this suggest that the
max size should always be chosen for text fields?
Click to expand...


Definitely not. Rather, what it is implying is that if the text length
varies but not by much e.g.

control_status VARCHAR(8) DEFAULT 'Enabled' NOT NULL
CHECK control_status IN ('Enabled', 'Disabled')

it might be better to use CHAR(n).

Note that CHAR(n) is available in Jet 4.0 but only in ANSI-92 query
mode (or equivalent e.g. ADO) so it doesn't seem to be widely used at
present (the same applies to VBA's String * n type but for different
reasons, of course).

...or should I say NCHAR(n), because Jet text columns are Unicode by
default. To get an ASCII *equivalent* you need to use e.g. (ANSI-92
query mode only):

control_status CHAR(8) WITH COMPRESSION DEFAULT 'Enabled' NOT NULL

however, such a column still appears in the Information Schema as a
"null-terminated Unicode character string" so perhaps I should remove
the word "by default" from my earlier statement :(


I fudged it, should have been

SELECT CAST('2003-02-28T00:00:00' AS DATETIME);

because '2003-02-28' is an unsafe format in SQL Server (but not in
Jet). Anyhow, I don't see how you can achieve the above syntax in VBA.
Click to expand...

Maybe:

SELECT CAST("'2003-02-28T00:00:00' AS DATETIME");

where a UDF called Cast parses the string and converts it into a date
based on the part after 'AS.'
Jamie.

--
Click to expand...

Many thanks for your comments. I suppose it didn't hurt for me to
select and enforce reasonable sizes for text fields in Access instead of
using 255 everywhere. SQL Server seems every bit as smart as Access
when it comes to data storage :-). I feel like I'm barely starting my
journey into T-SQL since the little bit of T-SQL I've used so far has
looked much like Access SQL. You've helped me get started on that
journey. I look forward to interacting with you again in future threads.

James A. Fortune
(e-mail address removed)

That time of year thou may'st in me behold
When yellow leaves, or none, or few, do hang
Upon those boughs which shake against the cold
Bare ruin'd choirs, where late the sweet birds sang.
-- Shakespeare, Sonnet 73
 
Back
Top