Setting up a table with a calculated column

Question

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

Answer 1

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

Answer 2

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.