What is the best way to store time in MySQL over TIME range?

Question

I need a method to store time in a db field. I am creating a website where customers should be able to choose how long they will show ads from a specific start date.

I was thinking about using TIME, but it had a maximum of 838: 59: 59, which works after about 34 days. Perhaps the customer wants the ad to exist longer than this.

So what would be the best way to handle this? Just a really big int?

Answer 1

If you are going to have a column for the start time and one for the duration, I think you can save it in seconds. So, I assume that you will have something like this:

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

(For example, we will call this table adverts )

• advert_id is the key pointing to your ad.
• start_time - start time of the ad (data type - TIMESTAMP)

• duration_seconds - The time, in seconds, during which the ad should "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 receive only those ads that have not expired, you will run this request:

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

This is one way to do this if I went with a duration field.

Answer 2

Yes, you can save time as an INT data type (or other large integer: MEDIUMINT, LONGINT). Then use you can easily get part of the time and time from this, for example. -

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

Where 86400 is the number of seconds for 24 hours (60 * 60 * 24 = 86400).

Answer 3

not the best solution, but you can add one column to your db and check when the time is more than 24 hours, calculate it as 1 day and write in this column, and write the rest of the time in the time column. But choosing from db, you should also calculate that the days column