Caveat: DATEs and DATETIMEs

A small bug/feature, which I created a new bug for at http://bugs.mysql.com/25706. Basically, the CURRENT_DATE() function seems to assign a time to dates, and that time is 00:00:00. In fact, all DATE formats are actually DATETIMEs with the date field of 00:00:00 and hidden.

This interferes with queries that use the date as an actual date and expect the date to include everything up until 23:59:59 of that day. The easiest way to reproduce this:

SELECT IF(NOW() BETWEEN '2007-01-17' AND '2007-01-18','yes','no') AS test\G
test: no
1 row in set (0.00 sec)

In fact, the following query always returns “no”, unless it’s exactly midnight:

SELECT IF(NOW() BETWEEN CURRENT_DATE() - INTERVAL 1 DAY AND CURRENT_DATE(),'yes','no') AS test\G
test: no
1 row in set (0.00 sec)

This does not make logical sense — NOW() should always be between “yesterday” and “today”. It’s one thing to be cautious not to compare DATEs and DATETIMEs to each other. However, this is a bit too much of a bug for me. And it’s not just the BETWEEN…AND syntax — that just converts to >= and < = to. For a test of this: SELECT IF(NOW() >= CURRENT_DATE() – INTERVAL 1 DAY AND NOW() < = CURRENT_DATE(),'yes','no') AS test\G test: no 1 row in set (0.00 sec)

Again, NOW() should fall between “yesterday” and “today”.

There is a workaround, although poor at best:

SELECT IF(DATE(NOW()) BETWEEN CURRENT_DATE() AND CURRENT_DATE(),'yes','no') AS test\G
test: yes
1 row in set (0.00 sec)

I found this with both MySQL 4.1.19-standard-log and 5.0.19-standard-log.

Now, (thanx to Roland) this is because when you cast a DATE to a DATETIME, it gets the 00:00:00 time put in. And when you’re comparing a DATE and a DATETIME, MySQL implicitly casts the DATE to DATETIME. It’s a wonderful gotcha, and I understand why it is, but I do not like it.

It also means that I have to go look at a whole bunch of reports…..

Oracle also works this way, and I’m guessing all db’s do, but I’d like confirmation if folks have any. What a pain.

Comments are closed.