List:General Discussion« Previous MessageNext Message »
From:Jigal van Hemert Date:July 29 2005 7:50am
Subject:Re: tmestamp resolution problem (rounding off to a day)
View as plain text  
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.
Thread
tmestamp resolution problem (rounding off to a day)Nick Sinclair29 Jul
  • Re: tmestamp resolution problem (rounding off to a day)Jigal van Hemert29 Jul
    • Re: tmestamp resolution problem (rounding off to a day)Nick29 Jul