Best way to store time interval values in MySQL?

Question

Stupid easy problem, but I haven't been able to find an elegant solution. I want to store time intervals in a MySQL columns, for instance:

1:40 (for one hour, 40 minutes) 0:30 (30 minutes).

Then be able to run queries, summing them. Right now I store them as INT values (1.40), but I have to manually do the addition (unless I'm missing an easier way).

The TIME column type only stores upto 900 hours (about, I think), so that's (almost) useless for me since I am tracking upwards of hundreds of thousands of hours (I store one field with a summation of many different entries).

Thanks!

Answer 1

I would simply store them in an INT field as seconds or minutes (or whatever the lowest time value you are working with).

As an example, you want to store the following time value 1 hr 34 min 25 seconds:

That is 5665 seconds. So just store the value as 5665 in an INT field

Later, lets say you want to store the time value 56 min 7 seconds:

That's 3367 seconds.

Sum up everything later: 5665 + 3367 = 9032 seconds

Convert that back to hours, minutes, seconds, you get 2 hrs 30 min 32 seconds. Yes, you have to do the math to make the conversion, but it's a very simple calculation and it's not at all machine intensive since you are only doing it once, either before you store the INT or once, after you retrieve the INT.

Answer 2

If you want to store them as hours, and thus keep the integer small, you can take the number of minutes and divide by 60.

So 1:40 becomes 100/60 = 1.66 , :30 becomes 30/60 = .5. Now you can just add them up normally:

  1.66 + .5 =  2.16

and then you have the number of hours as the whole, and the decimal part is the number of minutes over 60, so that would be

   .16 * 60 = 10

so it's 2:10

Your next best option is to see if MySQL can do base60.

Answer 3

I know this is not the kind of time interval you are talking about, when I when I searched around this is about the only relevant SO question I found, so to help out other lost souls like myself I thought I would post what I am doing now.

I am storing a Subscription length, which rather than precise values like "number of seconds" or even minutes is either Days or Months. So in my case I have a "duration" INT and a "duration_unit" ENUM of ('days','months').

So for a "6 month" subscription rather than trying to calculate how many minutes are in a 6 months (which varies depending on which 6 months you are talking about) I just store "6" and "months".

With PHP's mktime() method it is easy and more accurate to calculate time intervals of +6 months.

Answer 4

store just the minutes, so 1:40 gets stored as 100. this makes for easy addition: 100 + 30 = 130. when you display, do the math to convert back to hours:minutes. 130 minutes -> 2:10.

Answer 5

You can definitely use an int field for this, especially since MySQL provides the DATE_ADD and DATE_SUB functions for using integer units of time alongside date and datetime types in date artihmetic.

For example, if you have a datetime column eventDateTime and you have a duration column durationMinutes you can calculate the ending datetime using:

DATE_ADD(eventDateTime,INTERVAL durationMinutes MINUTE)