Handling and storing elapsed time

Question

I'm having problems deciding on what is the best way is to handle and store time measurements.

I have an app that has a textbox that allows the users to input time in either hh:mm:ss or mm:ss format.

So I was planning on parsing this string, tokenizing it on the colons and creating TimeSpan (or using TimeSpan.Parse() and just adding a "00:" to the mm:ss case) for my business logic. Ok?

How do I store this as in a database though? What would the field type be? DateTime seems wrong. I don't want a time of 00:54:12 to be stored as 1901-01-01 00:54:12 that seems a bit poor?

Answer 1

I think the simplest is to just convert user input into a integer number of seconds. So 54:12 == 3252 seconds, so store the 3252 in your database or wherever. Then when you need to display it to the user, you can convert it back again.

Answer 2

For periods less than a day, just use seconds as other have said.

For longer periods, it depends on your db engine. If SQL Server, prior to version 2008 you want a datetime. It's okay- you can just ignore the default 1/1/1900 date they'll all have. If you are fortunate enough to have sql server 2008, then there are separate Date and Time datatypes you can use. The advantage with using a real datetime/time type is the use of the DateDiff function for comparing durations.

Answer 3

Most databases have some sort of time interval type. The answer depends on which database you're talking about. For Oracle, it's just a floating point NUMBER that represents the number of days (including fractional days). You can add/subtract that to/from any DATE type and you get the right answer.

Answer 4

As an integer count of seconds (or Milliseconds as appropriate)

Answer 5

TimeSpan has an Int64 Ticks property that you can store instead, and a constructor that takes a Ticks value.

Answer 6

Are you collecting both the start time and stop time? If so, you could use the "timestamp" data type, if your DBMS supports that. If not, just as a date/time type. Now, you've said you don't want the date part to be stored - but consider the case where the time period spans midnight - you start at 23:55:01 and end at 00:05:14, for example - unless you also have the date in there. There are standard build in functions to return the elapsed time (in seconds) between two date-time values.

Answer 7

Go with integers for seconds or minutes. Seconds is probably better. you'll never kick yourself for choosing something with too much precision. Also, for your UI, consider using multiple text inputs you don't have to worry about the user actually typing in the ":" properly. It's also much easier to add other constraints such as the minute and second values conting containing 0-59.

Answer 8

and int type should do it, storing it as seconds and parsing it back and forth

http://msdn.microsoft.com/en-us/library/ms187745.aspx