February 14, 2008 at 11:56 am
Hi All
trust your having a good day.
Quick question, what is the best way to store hours and minutes in a table, not in a time sense, but in a sum sense.
For example i want to store 20,467 hours and 14 minutes
Do i store hours and minutes as seperate fields, or do i store 20,467.14 as a decimal, where i could limit the decimal to below .60 by entry rules
Thanks for your time
Ross
February 14, 2008 at 12:17 pm
if you don't store them in a datetime, you won't be able to add them up easily, or substract, etc. your minutes will always need so be adjusted and manually converted over with additions, and in reverse hours converted to minutes when you need to "borrow the 1" during a subtraction.
If you store it in some custom decimal form - you should at least store it in such a way that the part after the decimal point is expressed in terms of one of the units (say in hours). So - 14 minutes = 14/60=.23333333. That of course leads to rounding issues....If you express is in minutes, then you get into large integers to cover that.
At the end of the day - you still have to have conversion routines, custom adding schemes. Doesn't add up to any benefit over using the built-in type, with a lot of headaches to boot.
How you display them is up to you as far as what makes sense.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 14, 2008 at 12:23 pm
ross (2/14/2008)
Hi Alltrust your having a good day.
Quick question, what is the best way to store hours and minutes in a table, not in a time sense, but in a sum sense.
For example i want to store 20,467 hours and 14 minutes
Do i store hours and minutes as seperate fields, or do i store 20,467.14 as a decimal, where i could limit the decimal to below .60 by entry rules
Thanks for your time
Ross
Hello Ross,
It depends on the end usage and also either way you store there needs to be some logic built around it in order to get the end result. If stored in 2 different columns then you need write a simple logic to calculate the sum and on the other hand if stored in a single column then you need to write few more TSQL statements for further calculation purpose.
Hope this helps.
Thanks
Lucky
February 14, 2008 at 12:27 pm
I would store it in an int field as the number of minutes. The presentation as hours and minutes can be sone on querying, either in a proc or on the front end.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2008 at 12:34 pm
store only minutes as an integer. you can get hours and minutes with a simple formula.
total_min = (hr * 60) + min
hour = total_min / 60
min = total_min % 60
40 hr, 3 min = (40 * 60) + 3 = 2403
2403 / 60 = 40 hr, 2403 % 60 = 3 min
February 14, 2008 at 12:53 pm
Hi
What can I say,
I was just running some test examples and working out how to use at the front end.
Many thanks to all for your prompt help, it is very much appreciated.
Kind regards
Ross
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply