Perplexing php / Mysql Арифметическое поведение времени

Я пытаюсь выбрать все записи, введенные за последние девяносто секунд, и для каждой выбранной строки:

Покажите разницу между временем их вставки и текущим временем (как разностью)
Покажите количество секунд, оставшихся после записи, которые будут старше 90 секунд (как ожидается)

Структура таблицы:

attempt_id | username | attempt_ip | attempt_time

Это запрос, который я использую:

 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;

Но я получаю непоследовательные результаты:

Тестовый забег Пробный прогон

Другой тестовый прогон
введите описание изображения здесь

Полный код (если вы хотите попробовать) :

 <?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; } ?> в <?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; } ?>

TABLE CODE (если требуется):

 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 );

Примечание. Для вывода в реальном времени выполните с помощью командной строки.

 $php -f /path_to_script/script.php

Это потому, что вы выполняете неявное преобразование из mysql datetime в целое число.

Например, mysql считает, что время (как я пишу это) – это 2011-12-15 13:42:10, но если я попрошу mysql вычесть 90 из этого, это будет тренировка 20111215134210 – 90 = 20111215134120, которая составляет 13:41:20 50 секунд назад.

Либо обрабатывать время как целое число (путем преобразования в / из временной отметки unix, как предложено ликервикар) или использовать функции даты для выполнения математики по значению даты:

 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;

(обратите внимание, что я также переписал последнее выражение фильтра, так что столбец таблицы изолирован с одной стороны выражения – который имеет небольшую выгоду от скорости, когда столбец не индексирован, а огромный коэффициент при индексировании).

Или используя секунды-с-эпоху ….

 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);

(что, очевидно, не сможет использовать оптимизацию индекса).

Я сам это испытал, и у меня есть похожие результаты. Очевидно, что выполнение простых арифметических операций по датам в MySQL несовместимо (что на самом деле является ожидаемым). Вероятно, вы должны либо использовать функции даты MySQL, либо конвертировать ваши даты в метку времени. Я переписал ваш запрос как это

 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;

И это, похоже, отлично работает для меня. Я без проблем повторил до 63 с шагом в 2 секунды.