Determine if a row has recently been updated using MySQL

This is a fairly common case I think. You have a table that has a datetime field that gets updated when a row is updated – you need to determine if a row has been updated recently.

What I typically did in this case was return the datetime field and work out if the datetime value is a recent one or not, something like;

$date = $wpdb->get_var( $wpdb->prepare( "SELECT `date` FROM table WHERE `id` = %d", $id ) );

if ( ( time() - strtotime( $date ) ) < ( 60 * 1 ) ) {
    //row was updated less than a minute ago
}
else {
   //row was updated more than a minute ago
}

This works ok until your database and web servers are set to different timezones – this will mean the php’s time() will not match up to MySQL’s NOW(). So when you get the date field from the database and subtract it from whatever time is on your server, it’s possible that it’s inaccurate.

A way round this is to handle the date comparison in the MySQL query like this;

if ( $wpdb->get_var( $wpdb->prepare( "SELECT `date` FROM table WHERE `id` = %d AND `date` > DATE_ADD( NOW(), INTERVAL -1 MINUTE )", $id ) ) ) {
    //row was updated less than a minute ago
}
else {
   //row was updated more than a minute ago
}