Dan
Dan

Reputation: 29375

Handling and storing elapsed time

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?

Upvotes: 8

Views: 4575

Answers (8)

Jason DeFontes
Jason DeFontes

Reputation: 2285

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

Upvotes: 9

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

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.

Upvotes: 3

BlackTigerX
BlackTigerX

Reputation: 6146

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

Upvotes: 0

Kibbee
Kibbee

Reputation: 66162

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.

Upvotes: 0

Ken Ray
Ken Ray

Reputation: 2528

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.

Upvotes: 0

James Curran
James Curran

Reputation: 103595

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

Upvotes: 0

nsayer
nsayer

Reputation: 17047

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.

Upvotes: 0

davr
davr

Reputation: 19147

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.

Upvotes: 3

Related Questions