Which Mysql Data Type To Use For Scheduling?

April 01, 2024 Post a Comment

I want to record the start and end times of, say, movies playing at the cinemas. I need to store the date (e.g. Jan 1, 2011), start time (e.g. 9:00am), and end time (e.g. 11am). Sh

Solution 1:

In my opinion a person should use the datatype that better fits and describes the reality. In this case I would like to use Datetime.

Solution 2:

For me better is to use to DateTime columns, because INT have hidden interpretation. Without documentation you don't know what is it really: second, minutes, months, ... It is only design approach.

When using DATETIME for start and end fields take into consideration that you may have trouble when "calculationg how long process take", because you should also think about timezones, daylight saving, etc.

Solution 3:

Use DATETIME or INT

I prefer to store date&time in unix timestamp (INT), because i can use it in PHP functions like date

echo"Movie will start at ".date("H:i", $row['start_time']);

And it's easy to manipulate with it:

echo"Movie will start in ".(time() - $row['start_time'])." seconds";

Current date and time: http://www.unixtimestamp.com/

If you want to copy movies to next day:

$sql = "SELECT * FROM movies WHERE start_time >= ".strtotime("today 00:00")." AND start_time <= ".strtotime("today 23:59");

 // query..foreach($resultsas$row) {
    $new_start = strtotime("+1 day", $row['start_time'];    
    // insert
 }

Solution 4:

I would use 'timestamp' for both columns.

Datetime uses 4 bytes whereas Timestamp uses 8 bytes therefore more efficient.

Comparisons of timestamps is significantly faster than of datetimes.

When it comes to time zones leap years and daylight saving you may run into trouble with the int technique as you will have to take those changes into account manually. Maybe this won't be an issue for you.

Solution 5:

I'd say you have two appropriate formats:

"start time - duration" (datetime, int) or (timestamp, int)
"start time - end time" (datetime, datetime) or (timestamp, timestamp)

One of the big problems with date-time stuff is the DST jumps twice a year, throwing off your calculations by an hour. Traveling between timezones is equally confusing. If you use the time/time notation, your duration may vary. If you use the time/duration notation, your end time may vary. Whichever one of these two representations is the most appropriate therefore depends on your usage (you may need to use a hybrid approach).

For movie show times, I'd say storing a start time and a duration in minutes is the more appropriate format. The run-time of the movie is a given, the end time comes after the fact.

Lab Sql