Storing Time 1

[robertfah]

I need a way to store Days, Hours, and Minutes in my table but would like to know the best way to do this. ATM, the user is given the ability to enter Days, Hours, and Minutes for a given entry (each has it's own textbox on the GUI) so what do you think the best way is to store and retrieve the data easily? I am using SQL 2000.

Thanks!

 

[simian336]

Just to make sure we are on the same page.. When you say Days you do you mean a date or Mon, Tue, Wed, etc or some other scheme?

Simi

 

[robertfah]

Just to make sure we are on the same page.. When you say Days you do you mean a date or Mon, Tue, Wed, etc or some other scheme?

Sorry for the confusion....I meant all values are to be stored in numeric.

We have an issue system and for each issue, the user can enter an activity (1-many). So the GUI for the Activity entry allows the user to put a numerical value for Days, Hours, and Minutes. I've got to figure the best way to store this and retrieve it so it can be displayed the same way they entered it.

 

[gmmastros]

We're really talking about storing duration here, not necessarily "time" (as in, what time is it?). I would be tempted to store the duration as an integer. Before the data reaches the DB, multiple hours by 60, multiply days by 1440 (number of minutes in a day). Basically, store the number of minutes in the database. Everything else is just presentation, which belongs in the front end, not the database.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[robertfah]

Thanks George....That's what we've decided to go with, except we used a bigint because our front-end software uses a TimeSpan and can do all the calculations for us, so we can do something as simple as:

TimeSpan ts = new TimeSpan(Convert.ToInt64(dr["TimeSpent"]));

teDays.Text = ts.Days;
teHours.Text = ts.Hours;
teMinute.Text = tsMinutes;

where dr is a DataRow representing a row in the database.

Good stuff for sure! Thanks again!