Which is better: Date & Time in separate fields or both via DATETIME or TIMESTAMP

[WorldNews]

Hello,

Do you recommend that Date should be kept in a field with Type of DATE and time kept in a field with type of TIME
or do you recommend that both values be kept together in a field of DATETIME?

And in particular I am referring to when it comes programming the pages in say Php, is it cleaner/faster to have
the date values separate as Date from Time values?

Also is there any practical difference in size & speed between the field having type of DATETIME or TIMESTAMP?

[r937]

Do you recommend that Date should be kept in a field with Type of DATE and time kept in a field with type of TIME
or do you recommend that both values be kept together in a field of DATETIME?

definitely the latter

let's say that your table uses a single datetime column called salesdate

to retrieve all the sales between 6 pm friday july 13 and 9 am monday july 16 would require the following WHERE clause ¸--

WHERE salesdate BETWEEN '2012-07-13 18:00' AND '2012-07-16 09:00'

now let's say that your table uses separate date and time columns

as an exercise, try to come up with the WHERE clause yourself

Also is there any practical difference in size & speed between the field having type of DATETIME or TIMESTAMP?

whether there is a difference in speed is arguable (but i don't think that for practical purposes it matters much)

however, there is a huge difference in size, and a tremendous difference in the range of values that can be stored

of course, for full details, please see the manual

[WorldNews]

Hello,

1st, Thanks as usual for your insight.

So good thing I asked you, since I was about to launch this new service with DATE & TIME fields, but now I am going to go the other way.

To be sure for the 2nd part of the question, are you saying that data type DATETIME or TIMESTAMP is better? Faster and takes less room, etc.?

Regards,
Dean

definitely the latter
let's say that your table uses a single datetime column called salesdate

to retrieve all the sales between 6 pm friday july 13 and 9 am monday july 16 would require the following WHERE clause ¸--

WHERE salesdate BETWEEN '2012-07-13 18:00' AND '2012-07-16 09:00'

now let's say that your table uses separate date and time columns

as an exercise, try to come up with the WHERE clause yourself


whether there is a difference in speed is arguable (but i don't think that for practical purposes it matters much)

however, there is a huge difference in size, and a tremendous difference in the range of values that can be stored

of course, for full details, please see the manual

[r937]

To be sure for the 2nd part of the question, are you saying that data type DATETIME or TIMESTAMP is better? Faster and takes less room, etc.?

i did not, on purpose, indicate a preference

in fact, i pointed out that they are quite different, except perhaps in terms of speed

you really should read the manual