What is the right datatype to store Hours up to the minute precision?

[serge]

Right now the database I am working with is storing time in
an Integer data type and is storing the time value in seconds.
The application does not allow entering seconds. It accepts
minutes and hours.

I have a report where it is doing:

SELECT SUM(TIMEENTERED )

and the SUM is *blowing* up as the SUM is reaching
the BIGINT range.

I can fix the problem by changing all codes to:

SELECT SUM(CAST(TIMEEN TERED AS BIGINT))

But now that I ran into this problem I want to find out
if storing the time in seconds using INTEGER data
type is the best solution?

I've been searching this newsgroup and other places
the whole day. I even ran into my own three year old
post. Three years ago my problem was data migration
related and now it is more of performance related than
anything else.

http://groups.google.com/groups?as_q...=2006&safe=off

I could not find this specific topic in SQL books like
SQL for Smarties 2005 by Joe Celko (very good stuff on
temporal topics but nothing specific to my question),
or Inside SQL Server 2000.

Which data type would be ideal and why?

smalldatetime?
integer?
decimal?
float?

The type of operations that are being done in the database
are:

1- Entering time in hours on work done on a task
For the data entry part, the application accepts
2.5 as 2 and a half hours and it is storing
2.5 * 3600 = 9000 seconds.
It also accepts entering 2:30 as 2 hours and
30 minutes and again storing 9000 seconds.
I even saw a page where you can enter clock
time: I worked from 9:30AM to 12:45PM
as an example

When i checked the underlying table(s) I saw
that the ENTEREDTIME is always the duration
in seconds. So the data entry can either be

2.5 hours where ENTEREDTIME = 9000 seconds
or
9:00AM to 11:30AM
where STARTDATE is today's date for example
stored as 1/27/2005 09:00AM
and where ENTEREDTIME = 9000 seconds
2- All kinds of reports showing total time in hours
for example: Project1 = 18.5 hours
The code in the SP are all like:
SUM(ENTEREDTIME ) / CAST(3600 AS DECIMAL(6,2))
AS TOTALTIME

3- I am sure a lot of other arithmetic calculations are
being done with this ENTEREDTIME field.

What would be the best way to store hours/minutes
based on how we are using Time in the database?

Either I will stick with Integer but store in minutes
time instead of calculating in seconds and most likely
update all the SUM(ENTEREDTIME ) to
SUM(CAST(ENTERE DTIME AS BIGINT))
or I will switch to storing in decimal/float and
maybe avoid doing :

SUM(ENTEREDTIME ) / CAST(3600 AS DECIMAL(6,2))
AS TOTALTIME

since the ENTEREDTIME would already be stored
in hours time.
or I will use DATETIME since in the cases of

I worked from 9:00AM to 11:30AM

I have to have a separate column to store the date also.

I am a little confused I am hoping I will get some help
from you and maybe if I can't find the best solution, at
least eliminate the NOT so good ones I am thinking of.
Thank you

[Erland Sommarskog]

serge (se****@nospam. ehmail.com) writes:

What would be the best way to store hours/minutes
based on how we are using Time in the database?

Either I will stick with Integer but store in minutes
time instead of calculating in seconds and most likely
update all the SUM(ENTEREDTIME ) to
SUM(CAST(ENTERE DTIME AS BIGINT))

Since you appear to have an application that is working, I would do
as little change as possible.

Storing elapsed time as minutes rather than seconds makes sense if you are
not supporting seconds anyway. Then again, do the potential seconds
cause any menace?

Of course, if you store by minutes you may not need bigint, but that
depends on what that sum(enteredtime ) reflects. If you store by minutes,
you need 4085 years for an overflow, which is a lot for a single person. But
if the sum encompasses a workforce of 20000 employeess, it only takes
74 days per person to overflow.

Had we been talking about a new application, I can't say for sure what
I would recommend. All the datatypes have their tradeoffs. But keep in
mind that it's possible to have computed columns, so you could have
both a smalldatetime column and a minutes column if you like.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx