Whats the best way to store a time duration in a MySQL larger than the TIME range?

Question

I'm in need of a method to store a time duration in a db field. I'm building a website where customers should be able to choose how long they would like an advert to display from a particular start date.

I had thought about using TIME but that has a max of '838:59:59' which works out at about 34 days. Its possible that a client would want an advert to exist for longer than that.

So what would be the best way to deal with this? Just a really large INT?

Answer 1

If you intend to have a column for start time and one for duration, I think you can store it in seconds. So, I assume you will have something like this;

+-----------+--------------------------+------------------+
| advert_id | start_time               | duration_seconds |
+-----------+--------------------------+------------------+
| 2342342   |'2012-11-12 10:23:03'     | 86400            |
+-----------+--------------------------+------------------+

(For the sake of the example, we will call this table adverts)

1. advert_id - a key pointing to your advert
2. start_time - the time the advert should start (data type - TIMESTAMP)

3. duration_seconds - Time in seconds that the advert is supposed to "live" (INTEGER(11)

   SELECT TIME_TO_SEC(timediff(now(),start_time)) as 'time_difference_in_seconds_since_advert_started' FROM adverts;

If you want to get only adverts that have not expired, you will run a query like this;

SELECT * FROM  `adverts` WHERE TIME_TO_SEC(timediff(now(),start_time))<=`duration_seconds`;

That's one way I would do it if I were to go with the "duration" field.

Answer 2

Yes, you can store time as INT data type (or another big integer: MEDIUMINT, LONGINT). Then use you can easily get days and time part from this, e.g. -

SELECT time DIV 86400 AS days, SEC_TO_TIME(column1 MOD 86400) AS time FROM table

Where 86400 is a number of seconds in 24h (60 * 60 * 24 = 86400).