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?

Solution 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.

Solution 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).

Comments

[cosmicsafari]

This is pretty much exactly what im talking about, but it was more to do with what the best field type would be to store it rather than the application of doing so. As DevArt mentioned above, i could use BIGINT to get around the TIME range issue. Thanks for the answer though, its very well written. ^_^

[mwangi]

Ok cosmicsafari. Don't forget to select it as the correct answer ;)

[mtraceur]

Consider storing the end time instead of the start time, if your logic or your users are more interested in the end time than the start time in practice (if the end time is used more often than the start time, especially in separate code paths, then it can make code more readable and easier to understand, reduce odds of coding error, and maybe even improve performance). Like in this example, look how much clearer and simpler the WHERE clause can become: WHERE end_time > now();