Should I use DATE and TIME as fields rather than DATETIME

Question

I have a project on going for a TV guide, called mytvguide in the database - I use PHPMyAdmin. This is the structure for one table, which is called tvshow1:

Field       Type             
channel     varchar(255)             
date        date    No               
airdate     time    No               
expiration  time    No               
episode     varchar(255)     
setreminder varchar(255)    

but am not sure how to get DATE, TIME to work with the pagination script (below is the script, which works for the version with DATETIME): http://pastebin.com/6S1ejAFJ

However, although the DATETIME one works - it shows programmes that air on the day itself like this:

Programme 1 showing on Channel 1 2:35pm "Episode 2" Set Reminder
Programme 1 showing on Channel 1 May 26th - 12:50pm "Episode 3" Set Reminder
Programme 1 showing on Channel 1 May 26th - 5:55pm "Episode 3" Set Reminder

but I'm not quite sure how to replicate that for the fields that use DATE, TIME functions as seen above.

Any advice on this is appreciated, thanks!

Answer 1

Answering to your question title:

datetime type is quite handy, and you always can format this field using date() or time() functions to get the appropriate part, or any other function from the huge list

As for the question body, field type has nothing to do with pagination. Got a particular question?

Answer 2

In MySQL you can use DATE_FORMAT (see manual)

For instance:

SELECT DATE_FORMAT(airdate, "%d %M %Y - %H:%i") FROM programs WHERE ....

Otherwise you just pull the value as it is and modify it with the time/date function that PHP provides

Answer 3

If you ever want your database to scale, you should follow one important rule: Avoid per-row functions, like if, coalescse, case and yes, date() and time().

An attribute (column) in a table should be the smallest unit you handle. If you will need to search on date, store the date separately. Don't combine it into a datetime where you will have to extract it in your select statements.

People who combine attributes into a single fields such as date into a datetime or, horror of horrors, a comma-separated-list which you want to check individual parts of, are doing themselves and their successors a huge disservice.

You're usually better off pasting two fields together (such as date and time into a datetime for your pagination script) than trying to split the other way (for other purposes).

Of course, if you never intend to use the date and time separately in your queries, or if you expect your database to remain small, feel free to ignore my rant :-)

Answer 4

That's is not an answer to your question, but it's too long to be posted as a comment. ;)

If channel column is something like Discovery, MTV or CNN then you should create another table for those channels, and create 1-n relationship between channel and tvguide tables. Also episode is probably 2 or S4E12 (season 4, episode 12) so it should be column (or columns for seasion and episode) of numeric type. Finally a specific TV programme shouldn't be defined in tvguide - that's two completely different things. You should have one table for TV programme and another that joins channel, programme and define air date.

Is suggest read An Introduction to Database Normalization.