473,320 Members | 2,164 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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(TIMEENTERED 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(ENTEREDTIME 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
Jan 28 '06 #1
1 7364
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(ENTEREDTIME 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****@sommarskog.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
Jan 28 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Arne | last post by:
How do I pass a dataset to a webservices? I need to submit a shoppingcart from a pocket PC to a webservice. What is the right datatype? II have tried dataset as a datatype, but I can't get it to...
4
by: Dabbler | last post by:
Not sure what SQL datatype should be used with radiobuttons since MS SQL doesn't support a boolean datatype. I would like to bind radiobuttons to an SqlDataSource. Thanks for any suggestions on...
0
by: graju80 | last post by:
I am kind of new to Db2... Question: What are the rules that DB2 uses to determine the right datatype for a particular column for on-the-fly SQL generation? For example...
4
by: Jess | last post by:
Hello, I'm doing some calculations with great amount of precisions required. Can somebody please suggest some suitable libraries or classes to use? Thanks, Jess
4
by: brazil.mg.marcus.vinicius.lima | last post by:
Hello, I need to create a column that will store hours bigger than 24. For example '25:00:00', '129:23:12', etc). That column will be used too, for perform calculations between datetime...
1
by: Nathan Sokalski | last post by:
When a DataTable is filled using something such as a DataAdapter, what values are assigned to each of the DataColumns' DataType property for the different SQL types? Thanks. -- Nathan Sokalski...
2
by: =?Utf-8?B?Q2xhdWRl?= | last post by:
How can I get the complet schema of a datacolumn using ADO.Net objects (the equivalent of ADOX in ADO 2.8) ? I need to know the DataType, the DataSize, the Precision... The DataColumn object...
8
by: ganesh22 | last post by:
Hi, in my database i gave a datatype as float so i entered a value as 3.10 but it taking as 3.1 and also i entered as 3.999 but it will taking as 4 how its possible which datatype store the...
1
by: djminus1 | last post by:
Hi, I am struggling to find a methodology for formatting the store hours for a restaurant that I am developing a simple CMS for. I am using codeIgniter, but it's sort of irrelevant. Basically,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.