Forums.ATC.no
Teknisk => Generelt teknisk => Emne startet av: ATC på 31. Oktober 2008, 13:32 pm
-
The column type 'datetime' can be a real time-saver, but selecting records of a certain age can be tricky.
-
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.
-
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.