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

duration, mysql, time

Solution

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`)

- advert_id - a key pointing to your advert

- start_time - the time the advert should start (data type - TIMESTAMP)

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.

Problem

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?

Original source