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
July 16, 2012, 11:57am
2
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
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
r937:
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
July 17, 2012, 12:24am
4
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