Confused about comparing date fields with literals

Question

I am puzzled by the behaviour below. Why does the first SELECT statement return 1 while the second statement returns 0? I expect them both to return 1 as the date is greater than or equal to the literal.

Why does collation affect date comparison? When comparing dates against literals, is it wrong to represent the date (or date time) as a string? If so how should I be doing date vs literal comparisons?

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> USE test;

mysql> SET NAMES utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE foo (
  bar date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO foo (bar) VALUES ('2013-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(*) FROM foo WHERE bar >= '2013-01-01 00:00:00';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> SET NAMES utf8 COLLATE utf8_unicode_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM foo WHERE bar >= '2013-01-01 00:00:00';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

Answer 1

Have you tried

SELECT COUNT(*) FROM foo WHERE bar >= _utf8'2013-01-01 00:00:00'

Explanation's here

Answer 2

You probably assume that the db is doing an IMPLICIT type converion of your literal string into a date and comparing the dates. It looks like the db is doing an IMPLICIT type conversion of the date into a string and comparing the strings. The collation affects this conversion and so affects your result.

Try:

SET NAMES utf8 COLLATE utf8_general_ci;

SELECT * FROM foo;

SET NAMES utf8 COLLATE utf8_unicode_ci;

SELECT * FROM foo;

The two queries should give different results that explain the behaviour.

In any case cha's suggestion will work because you are telling the db to EXPLICITLY convert the literal string to a date and then compare the dates.