Best way to store span on time in a MySQL database?

Question

I'd like to store an amount of time for something in a mysql database,for the length of time something took, such as 2 hours, 5 minutes, 10 seconds. accuracy down to the second is preferred, but not required. What's the best way to go about doing this?

Answer 1

I like storing things in the database as base units. That way I can reliably and accurately convert to anything else I want later on.

The only thing you have to worry about with this method is getting your precision down. If you are storing distance traveled for cross country trips, inches or centimeters might not be what you are looking for.

In your case, I'd store the span as seconds or milliseconds and go from there.

Answer 2

A MySQL TIME type can store time spans from '-838:59:59' to '838:59:59' - if that's within your required range, and it would be useful to have MySQL natively present these spans in HH:MM:SS form, then use that.

Otherwise, I'd just go with an integer type to represent the number of seconds.

Answer 3

As Andrew says, an integer field with seconds might do already.

If the data is available, and it makes sense to store it, I would save the data in two mySQL timestamps with the start and end times (also possibly great for auditing later), and use DATE_DIFF() to fetch the actual duration.