MySQL: Comparing datetime columns

[ATC]

The column type 'datetime' can be a real time-saver, but selecting records of a certain age can be tricky.

[ATC]

First of all, make 100% sure time is perfectly syncronized between the mysql server and its clients.

SUBDATE() only accepts datetime objects or the INTERVAL keyword:
"SELECT * FROM table WHERE SUBDATE(NOW(), INTERVAL 7 DAY) < column;"
The INTERVAL keyword accepts one (and only one) of the following: YEAR, MONTH, DAY, HOUR, MINUTE and SECOND.

SUBTIME on the other hand lets you specify the time as a string:
"SELECT * FROM table WHERE SUBTIME(NOW(), '7 0:0:0.0000') < column;"
This allows more fine-grained control but can't express months or years.

Finally, while you COULD reverse the expression like so;
"SELECT * FROM table WHERE SUBDATE(column, INTERVAL 7 DAY) > NOW();"
this would be a BIG MISTAKE because you would make one expensive SUBDATE() call for every single record. The SUBDATE(NOW(),...) expression needs only be called once.

[Floyd-ATC]

Actually, using > and < comparison on datetime columns is dangerous. Although MySQL documentation suggests it, in real life it will bite you. Hard. I spent well over a week trying to figure out why incorrect rows were being selected. It probably has to do with the magic conversion going on in the background, but I could never pin it down.

Instead, use the excellent BETWEEN...AND construct like so:

WHERE column BETWEEN SUBDATE(NOW(), INTERVAL 7 DAY) AND NOW()

If you really must use > and <, I strongly suggest using unixtime.