Incorrect result for date comparison (to a timestamp column) in mysql

Question

I've been having some 'strange' results while comparing dates.
table1 has two rows with TIMESTAMPS values 2009-08-26 23:39:56 and 2009-08-27 00:01:42
When I make this query:

select * from table1 c
INNER JOIN table2 r ON r.table1_id = c.id
WHERE DATE(c.authorization_date) = '2009-08-26'

it returns both rows (when it only should have returned one).

For added weirdness the rows in the returned resultSet have tha same value: 2009-08-26 23:39:56

But If I make this query:

SELECT DATE(authorization_date) FROM table1

It correctly returns two rows with values 2009-08-26 and 2009-08-27

So, here comes my questions. How could I make the comparison so the correct result is returned, what am i doing wrong? Could be related to the inner join?

Answer 1

I suspected:

• some timezone discrepency, but you seem to have accounted for that

• some other data that is confusing the problem... is there something else that could be interfering?

Wish I could recreate this and help. Here's my setup code. What am I missing?

mysql> create table table1 (id integer primary key auto_increment, authorization_date TIMESTAMP);
mysql> insert into table1 values (1, '2009-08-26 23:39:56');
mysql> insert into table1 values (2, '2009-08-27 00:01:42');
mysql> create table table2 (table1_id integer);
mysql> insert into table2 values (1);
mysql> insert into table2 values (2);


    mysql> SELECT DATE(authorization_date) FROM c;
    +--------------------------+
    | DATE(authorization_date) |
    +--------------------------+
    | 2009-08-26               | 
    | 2009-08-27               | 
    +--------------------------+

    mysql> select * from table1 c INNER JOIN table2 r ON r.table1_id = c.id WHERE DATE(c.authorization_date) = '2009-08-26';
    +----+---------------------+-----------+
    | id | authorization_date  | table1_id |
    +----+---------------------+-----------+
    |  1 | 2009-08-26 23:39:56 |         1 | 
    +----+---------------------+-----------+
    1 row in set (0.00 sec)