CODEWITHSUNDEEP
databaseruby-on-rails-3database-designactiverecordtime
Mutuelinvestor
Mutuelinvestor

Reputation: 3538

What's the best way to store elapsed times in a database

I working on a horse racing application and have the need to store elapsed times from races in a table. I will be importing data from a comma delimited file that provides the final time in one format and the interior elapsed times in another. The following is an example:

Final Time: 109.39  (1 minute, 9 seconds and 39/100th seconds)

Quarter Time: 2260  (21 seconds and 60/100th seconds)

Half Time: 4524     (45 seconds and 24/100th seconds)

Three Quarters: 5993   (59 seconds and 93/100th seconds)

I'll want to have the flexibility to easily do things like feet per seconds calculations and to convert elapsed times to splits. I'll also want to be able to easily display the times (elapsed or splits) in fifth of seconds or in hundredths.

Times in fifths: :223  :451  :564      1:091 (note the last digits are superscripts)

Times in hundredths: 22.60  :45.24  :56.93   1:09.39

Thanks in advance for your input.

Upvotes: 5

Views: 1940

Answers (4)

sarnold
sarnold

Reputation: 104040

I think the 109.39 representing 1 min 9.39 sec is pretty silly. Unambiguous, sure, historical tradition maybe, but it's miserable to do computations with that format. (Not impossible, but fixing it during import sounds easy.)

I'd store time in a decimal format of some sort -- either an integer representing hundredths-of-a-second, as all your other times are displayed, or a data-base specific decimal-aware format.

Standard floating point representations might eventually lead you to wonder why a horse that ran two laps in 20.1 seconds each took 40.200035 seconds to run both laps combined.

Upvotes: 1

tylerl
tylerl

Reputation: 30847

It all depends on how you intend to use it, but number of elapsed seconds (perhaps as a float if necessary) is certainly a favorite.

Upvotes: 1

Chris Eberle
Chris Eberle

Reputation: 48775

Generally timespans are either stored as (1) seconds elapsed or (2) start / end datetime. Seconds elapsed can be an integer or a float / double if you require it. You could be creative / crazy and store all times as milliseconds in which case you'd only need an integer.

Upvotes: 6

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

If you are using PostgreSQL, you can use interval datatype. Otherwise, any integer (int4, int8) or number your database supports is OK. Of course, store values on a single unit of measure: seconds, minutes, milliseconds.

Upvotes: 3

Related Questions