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.



Reply via email to