Jigal van Hemert wrote:
Nick Sinclair wrote:
"[.] WHERE date_format(timestamp, '%Y-%m-%d %T')
<=DATE_SUB(CURDATE(),INTERVAL $BLACKLIST_DECAY HOUR)"
* ...It only resolves to the DAY and not an hourly resolution. I have
included a script below that I use for debugging, the MySQL
functionality is taken directly from one of the larger scripts in my
package. This is apparent, as the only time any entries are removed,
is once a day, as the number of hours is "rounded off" to a day, and
I can see this relected in the logs. Also, FYI, the
"$BLACKLIST_DECAY" variable from the sourced config file is in "hours".
I don't know the type of the field "timestamp", but I suspect that the
following will work better and faster:
WHERE `timestamp` <= NOW() - INTERVAL $BLACKLIST_DECAY HOUR;
CURDATE() gives you a 'timestamp' of the beginning of today (only date
part with time part as 00:00:00)
NOW() gives you a 'timestamp' of this moment (including time part)
The MySQL optimizer will see that the expression after the <= is a
constant and will produce a fast query (instead of calculating the
DATE_FORMAT() for each row in de table) which can use an index.
Regards, Jigal.
Thanks Jigal, all works great - You are *so* right.
