Using a duration field in a Rails model

Question

I'm looking for the best way to use a duration field in a Rails model. I would like the format to be HH:MM:SS (ex: 01:30:23). The database in use is sqlite locally and Postgres in production.

I would also like to work with this field so I can take a look at all of the objects in the field and come up with the total time of all objects in that model and end up with something like:

30 records totaling 45 hours, 25 minutes, and 34 seconds.

So what would work best for?

• Field type for the migration
• Form field for the CRUD forms (hour, minute, second drop downs?)
• Least expensive method to generate the total duration of all records in the model

Answer 1

• Store as integers in your database (number of seconds, probably).
• Your entry form will depend on the exact use case. Dropdowns are painful; better to use small text fields for duration in hours + minutes + seconds.
• Simply run a SUM query over the duration column to produce a grand total. If you use integers, this is easy and fast.

Additionally:

• Use a helper to display the duration in your views. You can easily convert a duration as integer of seconds to ActiveSupport::Duration by using 123.seconds (replace 123 with the integer from the database). Use inspect on the resulting Duration for nice formatting. (It is not perfect. You may want to write something yourself.)
• In your model, you'll probably want attribute readers and writers that return/take ActiveSupport::Duration objects, rather than integers. Simply define duration=(new_duration) and duration, which internally call read_attribute / write_attribute with integer arguments.