困惑的php / Mysql时间算术行为

I am trying to SELECT all entries entered in last ninety seconds and for each row thus fetched:

• Show the difference between their insertion time and the current time (as diff)
• Show the number of seconds left post which the record will be older than 90 secs (as pending)

Table structure:

attempt_id    |   username   |   attempt_ip   |   attempt_time  

This is the query I am using :

SELECT *, (NOW() - attempt_time) diff, ( 90 + attempt_time - NOW() ) pending, 
NOW() nw FROM failed_login WHERE (username = 'some_username' 
OR attempt_ip = '127.0.0.1') AND NOW() - attempt_time < 90;

But I am getting inconsistent results:

Test Run

Another Test Run

Full code (if you wish to try):

<?php

date_default_timezone_set('UTC');

function please_monsieur_db($qry) 
{
    $con = mysql_connect("localhost", "root", "");
    if(!$con)
        die("Unable to connect. " . mysql_error());

    $db = mysql_select_db("temp_test");
    if(!$db)
        die("Unable to select database. " . mysql_error());

    $res = mysql_query($qry);
    if(!$res)
        echo "
Query failed: $qry" . mysql_error();

    return $res;
}

/* Insert 3 records with a time gap between 2 and 8 sec after each insert */
$k      = 0;
while($k != 3)
{
    $q      = "INSERT INTO failed_login (username, attempt_ip) VALUES ('some_username', '127.0.0.1');";
    $rs     = please_monsieur_db($q);
    if($rs)
        echo "Insert @ " . time() . "
";
    sleep(rand(2, 8));
    $k++;
}

/*
 * SELECT all attempts in last ninety seconds and for each show the difference
 * between their insertion time and the current time (diff) and 
 * number of seconds left post which the record will be older than 90 secs.
 * Output the status every 2 seconds
 */
$m = 1;
while($m)
{
    $query  = "SELECT *, (NOW() - attempt_time) diff, (90 + attempt_time - NOW()) pending, NOW() nw  FROM failed_login 
            WHERE (username = 'some_username' OR attempt_ip = '127.0.0.1') AND NOW() - attempt_time < 90;";

    $res    = please_monsieur_db($query);

    if(!$res)
        exit;

    $ct     = mysql_num_rows($res);
    echo "
";
    while($row = mysql_fetch_array($res))
    {
        echo "Now:" . strtotime($row['nw']) . " || Attempt Time: " . strtotime($row['attempt_time']) . 
        " || Diff: [NOW() - attempt_time] = " . $row['diff'] . " || Pending [90-Diff] = : " . $row['pending'] . "
";
    }
    echo "
";
    sleep(2);
    $m = $ct;
}
?>

TABLE CODE (if you require):

CREATE DATABASE temp_test;
DROP TABLE IF EXISTS failed_login;

CREATE TABLE failed_login (
   attempt_id      INT AUTO_INCREMENT PRIMARY KEY,
   username        VARCHAR(256),
   attempt_ip      VARCHAR(16),
   attempt_time    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Note: For real time output, run using command line.

$php -f /path_to_script/script.php  

It's because you're doing an implicit cast from a mysql datetime to an integer.

E.g. mysql thinks the time (as I write this) is 2011-12-15 13:42:10 but if I asked mysql to subtract 90 from this, it would workout 20111215134210 - 90 = 20111215134120 which is 13:41:20 Which is 50 seconds ago.

Either treat the time as an integer (by converting to/from a unix timestamp, as suggested by liquorvicar) or use the date functions to do maths on a date value:

SELECT *, 
timediff(NOW(), attempt_time) diff, 
timediff(NOW(), attempt_time + INTERVAL 90 SECONDS) pending,
NOW() nw 
FROM failed_login 
WHERE (username = 'some_username' 
     OR attempt_ip = '127.0.0.1') 
AND NOW() - INTERVAL 90 SECONDS > attempt_time;

(note that I've also rewritten the last filter expression such that the table column is isolated on one side of the expression - which has a small speed benefit when the column is nor indexed but a huge beneift when it is indexed).

Or using seconds-since-epoch....

SELECT *, 
UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(attempt_time) diff, 
UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(attempt_time) + 90 pending,
NOW() nw 
FROM failed_login 
WHERE (username = 'some_username' 
     OR attempt_ip = '127.0.0.1') 
AND UNIX_TIMESTAMP(NOW()) - 90 > UNIX_TIMESTAMP(attempt_time);

(which obviously won't be able to use index optimization).

I've tested this myself and I get similar results to you. Obviously doing straight arithmetic operations on dates is not consistent in MySQL (which actually is kind of expected). You should probably either use the MySQL date functions or convert your datetimes to timestamp. I rewrote your query as this

SELECT 
   *, 
   (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(attempt_time)) diff, 
   (UNIX_TIMESTAMP(attempt_time) + 90 - UNIX_TIMESTAMP()) pending, 
   NOW() nw  
FROM failed_login 
WHERE (username = 'some_username' OR attempt_ip = '127.0.0.1') 
HAVING diff < 90;

And that seems to work fine for me. I've iterated up to 63 in 2 second increments without a problem.