Strange problems with MySQL datetime query

Question

How do these results make sense? I don't understand.
Shouldn't for example the last query return an empty set since that there are no threads with date > 2010? Why does it return a result from 2003?

mysql> SELECT * 
       FROM thread 
       WHERE newsgroup_id = '64654' 
       AND 'thread_date' < '2010-09-10 21:43:05' 
       LIMIT 1;

Empty set (0.00 sec)

mysql> SELECT * 
       FROM thread 
       WHERE newsgroup_id = '64654' 
       AND 'thread_date' < '2000-09-10 21:43:05' 
       LIMIT 1;

Empty set (0.00 sec)

mysql> SELECT * 
       FROM thread 
       WHERE newsgroup_id = '64654' 
       AND 'thread_date' > '2000-09-10 21:43:05' 
       LIMIT 1;
+--------------+-----------+-----------+----------+---------------------+---------------------+---------------------------------+-------------------------+
| newsgroup_id | thread_id | postcount | hash     | thread_date         | thread_date_last        | thread_title                    | title_has_valid_charset |
+--------------+-----------+-----------+----------+---------------------+---------------------+---------------------------------+-------------------------+
|        64654 |         1 |         0 | O2gvcPRl | 2003-06-06 22:51:24 | 0000-00-00 00:00:00 | Vendo fotodigit  2.1  megapixel |                       0 |
+--------------+-----------+-----------+----------+---------------------+---------------------+---------------------------------+-------------------------+

1 row in set (0.00 sec)

mysql> SELECT * 
       FROM thread 
       WHERE newsgroup_id = '64654' 
       AND 'thread_date' > '2010-09-10 21:43:05' 
       LIMIT 1;
+--------------+-----------+-----------+----------+---------------------+---------------------+---------------------------------+-------------------------+
| newsgroup_id | thread_id | postcount | hash     | thread_date         | thread_date_last    | thread_title                    | title_has_valid_charset |
+--------------+-----------+-----------+----------+---------------------+---------------------+---------------------------------+-------------------------+
|        64654 |         1 |         0 | O2gvcPRl | 2003-06-06 22:51:24 | 0000-00-00 00:00:00 | Vendo fotodigit  2.1  megapixel |                       0 |
+--------------+-----------+-----------+----------+---------------------+---------------------+---------------------------------+-------------------------+

1 row in set (0.00 sec)

If I'm correct, you can't do this. This is just an ASCII comparison (text based) and that's why you are getting these weird but 'correct' comparisons. Look up how to compare date/time in mysql — PhD
– PhD, Commented Aug 14, 2011 at 18:49
@Nupul: you do can compare datetime as if they were strings in MySQL — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Aug 14, 2011 at 18:55
@ypercube: My bad...should have clarified. I meant the overall comparison is string based (both LHS and RHS of <,>) — PhD
– PhD, Commented Aug 14, 2011 at 19:19

Silver Light · Accepted Answer · 2011-08-14 18:49:24Z

7

You are using wrong type of quotes. MySQL thinks that you are comparing string "thread_date". Here is how it should be:

`thread_date` > '2010-09-10 21:43:05'

This way MySQL knows that you mean field thread_date and compares it to the date at the right side of expression.

answered Aug 14, 2011 at 18:49

Silver Light

46.3k36 gold badges126 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

Or no backquotes at all: ... AND thread_date > '2010-09-10 21:43:05' ...

Lightness Races in Orbit · Accepted Answer · 2011-08-14 18:51:46Z

0

'thread_date' is a string, not the value of that field.

You meant:

`thread_date` > '2010-09-10 21:43:05'

answered Aug 14, 2011 at 18:51

Lightness Races in Orbit

387k77 gold badges670 silver badges1.1k bronze badges

Collectives™ on Stack Overflow

Strange problems with MySQL datetime query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related