| Author |
Topic |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2005-05-26 : 17:35:19
|
| Hello all. Need some advice from you.I need to have a column that will store time. When I say "time" I am talking like stopwatch time. I don't care about seconds, only hours and minutes. I need this for time tracking of events and figured that this would be a good way for workers to say that they spent 12 minutes on something (00:12). Eventually, they will need to see how much time they spent overall on a series of events. I need to be able to display that calculation in hours and minutes. How would you go about this?My current system has been a dropdown box of hours (0 - 24) and minutes (0 - 60). Each dropdown has a lookup table behind it that stores the value as text. Of course this means that my tables have to have two columns, one for the hours foreign key, and one for the minute foreign key.So, displaying it is easy, but doing calculations off of it is a pain. Is there a better way? I have created a "timepicker" control that only accepts hh:mm, but when it dumps it into the database, the time is wrong. If the hours are "00", then it puts 12 into the hours slot. So a time of 00:12 comes into the database as 12:12 AM. Am I pretty well forced to use a varchar field and get creative when it comes to calculating values?Thanks!Aj |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-05-26 : 17:58:34
|
quote:
Originally posted by ajthepoolman
I have created a "timepicker" control that only accepts hh:mm, but when it dumps it into the database, the time is wrong. If the hours are "00", then it puts 12 into the hours slot. So a time of 00:12 comes into the database as 12:12 AM. Am I pretty well forced to use a varchar field and get creative when it comes to calculating value.
DATETIME, or better SMALLDATETIME are what you need. Your confusion using the fields isn't unique. Get used to working with 'em and they'll be great.Check BOL for information: quote:
Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.
Sounds difficult, but when you combine these datatypes with the built-in SQL functions:DATEADD and DATEDIFFThere's great flexability in calculating the results you described.There have been so many posts on this subject, you should try the search option in the Developer forum. Post back here if you still need help. |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-05-26 : 17:59:29
|
| The calculations should not be too painful.You could store them as int columns though,with check constraints: hour between 0 and 24, minute between 0 and 59The way I see it is that the values are numeric, and should be stored as such.What have You got so far ???:[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]rockmoose |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-05-26 : 18:08:00
|
| Sam, I am not so sure about the DATETIME datatypes in this case.For just storing a hh:mm duration, and not having to calculate timespans etc..I think it might be overkill to use those.Of course if one needs to keep track of start & stop times, then it's another issue.rockmoose |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-05-26 : 18:40:23
|
| AJ,You have not given us enough information. My inital thought (like roockmoose) is a numeric column as you are talking in terms of a timespan versus a timepoint.What type of numeric to use as your base type needs further info...precision?, highest possible value?, lowest possible value?...etc...DavidMA front-end is something that tries to violate a back-end. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-05-26 : 19:26:17
|
| I'm biased a little by the idea that it's better to learn to minipulate time variables instead of putting it off. It's a skill that's useful all throughout SQL programming (at least, it has been for me).Nothing wrong with using INTs, and it doesn't require learning anything new, but Aj already has a DATETIME field setup... |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-05-26 : 20:39:57
|
| Here is the situation, this application helps counselors track time with clients. Some of this time is direct, as in face to face visits, but most of the time will be indirect (paperwork, transport time, etc...). It would be very easy to have two columns, one for Start time, and one for End time. But when tracking indirect time, this would be sort of a pain. It would be easier for them to just say, I spent 1 hour and 24 minutes filing paperwork. Currently, my Visual Basic application has two dropdown boxes (comboboxes). One dropdown is for hours, 0 - 24. The other dropdown is for minutes 0 - 59. These dropdowns are populated by two lookup tables, tblHours and tblMinutes. It would be very easy to simply put two comboboxes on the form and have them record time that way. It is quick and easy for them to select two values from dropdowns. But as I began thinking more about this, I realized that my combobox values are stored in varchar fields in the lookup tables. And we all know that if you add the varchar values of 5 and 5 together, you get 55, not 10. Now, can I get around that with CAST and CONVERT functions? Sure, but before I put myself through that extra coding, I thought I would consult with you guys first. Afterall, I can't be the first guy to have to store this type of information.The "highest possible value" in this situation probably won't exceed 20 hours. The lowest possible value would be 1 minute. I know that doesn't help much, but someone above mentioning wanting to know this.I will do some searching through the Developers forum. I usually try to do that, but time was short today so I took the lazy route!Aj |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-05-26 : 21:23:07
|
| Lets forget the db for a sec...As someone who has built and used these types of time tracking stuff the biggest issue is with the UI... Keep it simple (you seem to be on the right track) and don't make it "feel" like the CIA is watching there every move.. Seriously, the word "Approximately" somewhere on the screen does wonders to ease user mood...Happy users = Good data.DavidMA front-end is something that tries to violate a back-end. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-05-27 : 02:21:32
|
Yes You can get around that with cast and convert, or You could use a numeric datatype in the database.David's KISS talk about approximability inspired me  :select top 80 right('0'+ltrim(number/4),2) + ':' + left(ltrim( number%4*15)+'0',2) as approx_time_spentfrom master.dbo.spt_valueswhere type = 'p' and number > 0approx_time_spent ----------------- 00:1500:3000:4501:0001:1501:3001:4502:0002:15..rockmoose |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-05-27 : 10:49:43
|
| So, let's talk more about this numeric data type. I admit that my experience with it has been limited to holding dollar amounts and such. I am assuming that the decimal point is what would replace the colon. Or can the colon be dumped right in, and the numeric type handles it?I swear I am not just fishing for easy answers here. I will put some of this to the test myself. I just wanted some general guidelines.Aj |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-05-27 : 11:43:16
|
| No,'00:15' would be a char(5), not numeric.Sorry for confusing You.I was thinking that minute precision would be needlessly precise for the user, ?and presenting the data like '00:15' (15 min interval) would be convenient for him.But if we stick to your original plan of storing hours + minutes in the lookuptables.Then I think that You should store them as int, (simply because the data they represent are whole numbers).By doing that You will not have to worry about casting from character data -> numeric data when doing calculations.rockmoose |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2005-05-27 : 14:24:58
|
| Ok, that clears it up for me! And here I thought that the answer was right under my nose with the numeric type!Aj |
|
|
|
|
Store and calculate time