Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm setting up a table in sql server 2008. It going to hold three columns (busTime, trainTime, carTime). I want these columns to hold the amount of time a user spends traveling per day. I then want to set up a calculated column that will work out the total time per day.

My question is what data type should I use for the three columns and if it's a weird data type that I've not used before (like "time") what would be the calculation that I would use on my calculated column.

Cheers

James

share|improve this question

migrated from stackoverflow.com Jan 3 '12 at 10:21

This question came from our site for professional and enthusiast programmers.

2 Answers 2

Why not just a smallint to represent "minutes"?
This is easily translated or formatted (in client code) or SUMmed etc

The actual conversion to minutes can be done in client code or as a DATEDIFF between start and end. I wouldn't use time personally for this because it won't process well (eg can't have > 23:59:59.9999999 if you want to see the monthly time

share|improve this answer

If the user just enters the busTime etc in minutes you can use an int, if you wanted to let the user enter in hh:mm you can probably convert that to (int) mins, you are rather vague on how the user will interface with the database.

To me this also makes more sense when reading the data i.e. minOnBus = 45 rather then busTime = 00:45 and makes it a tiny bit easier to query and aggregate.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.