Date format and SQL format
I have the following test entry:
The following query results in 0 records:
SELECT * FROM `events_dates` WHERE `start_date` = 21-12-2014
But the following query results in 1 entry:
SELECT * FROM `events_dates` WHERE `start_date` > 21-12-2014
I am a bit confused.
As a side question, is it okay to use a date type if I don't use a time field, or would you rather use "datetime"?
source to share
You need to digitize the date literals in the format 'YYYY-MM-DD'
. Otherwise, MySQL's interpretation is an arithmetic expression (integer subtraction):
21 - 12 - 2014 = -2005
A negative integer is -2005
added to the date value efficiently 0000-00-00 00:00:00
, which explains why your query with >
returns a string.
The correct SQL expression would be:
SELECT * FROM `events_dates` WHERE `start_date` = '2014-12-21'
As for using the type DATE
instead DATETIME
, yes, I would find it necessary to use it without the time part, if you know you're never going to use time. Although I would admit that I use most of the time DATETIME
for future extensibility. It really depends on your future needs.
If the issue is optimizing storage space to avoid saving time with help DATETIME
, consider how much potential this table needs to grow very large before worrying about optimizing it (this has to be really huge to make a difference). If you use DATETIME
this instead, you can often use the function DATE()
to truncate the values at query time. I believe this is just a very minor inconvenience.
The same query, if start_date
was a column DATETIME
, can use DATE()
to truncate a segment of time:
SELECT * FROM `events_dates` WHERE DATE(`start_date`) = '2014-12-21'
source to share