令人费解的php/mysql时间算术行为 [英] Perplexing php/Mysql Time arithmetic behaviour
问题描述
我正在尝试选择过去90秒钟内输入的所有条目,并针对这样提取的每一行:
I am trying to SELECT all entries entered in last ninety seconds and for each row thus fetched:
- 显示其插入时间与当前时间之间的时差(以diff表示)
- 显示记录要比90秒(待定)还早的剩余秒数
表结构:
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:
测试运行
另一个测试运行
完整代码(如果您想尝试):
<?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 "\nQuery 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() . "\n";
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 "\n";
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'] . "\n";
}
echo "\n";
sleep(2);
$m = $ct;
}
?>
表代码(如果需要):
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
推荐答案
这是因为您正在执行从mysql日期时间到整数的隐式转换.
It's because you're doing an implicit cast from a mysql datetime to an integer.
例如mysql认为时间(如我所写的)是2011-12-15 13:42:10,但是如果我要求mysql从中减去90,它将锻炼20111215134210-90 = 20111215134120这是13:41:20这是50秒前.
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.
要么将时间视为整数(通过转换到/从unix时间戳转换,如liquorvicar所建议),要么使用date函数对日期值进行数学运算:
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).
这篇关于令人费解的php/mysql时间算术行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!