|
|
Posted by serge on 10/01/15 11:38
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=store+hours+integer&num=10&scoring=r&hl=en&as_epq=&as_oq=&as_eq=&as_ugroup=comp.databases.ms-sqlserver&as_usubject=&as_uauthors=&lr=&as_drrb=q&as_qdr=&as_mind=1&as_minm=1&as_miny=1981&as_maxd=27&as_maxm=1&as_maxy=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
Navigation:
[Reply to this message]
|