BETWEEN ’2011-07-26 00:00:00′ AND ’2011-07-26 23:59:59′ is correct as long as timestamps are in 1 second resolution. Again: 23:59:59 is the *beginning* of the last second.
When you have better resolution than 1 second (as already exists with MariaDB), this makes the query wrong, since it will *not* catch 23:59:59.01, for example.

Perhaps I should have been more general and talk about “all the different kinds of temporal values, now and in the future”.
Do also note than not many programmers are actually aware of the differences between TIMESTAMP and DATETIME. They choose either; they work out the same queries for both (they look the same, don’t they?).

SELECT … WHERE timestamp_column BETWEEN ‘2001-07-26’ AND ‘2001-07-07’ AND timestamp_column < '2001-07-27';

As some query optimizers are happier with the BETWEEN construct (treating it as a more restrictive query than a pair of comparisons). This is simply because I try for the most portable queries that are effective.

+ date(timstamp_column)=’2011-07-26′ will work but this won’t catch the indexes.

BTW is it July 26th, 2011 or July 26th, 2001 (check ur query) !! 😉

@Baron
Yes, BETWEEN is inclusive, which is why it works correctly for now — for as long precision is 1 second.

@Roland,
Yes, leap seconds, daylight savings etc. are all so nicely managed by time/date math within MySQL, I usually drop all application based calculations and just ask the DB to do the math.

😉