MySQL：减去日期值和 NOW() 时出现奇怪的结果
我只是尝试用当前日期时间插入两行，然后计算自该日期以来经过的时间。 以下是两次插入并使用 NOW() 函数设置时间戳后表中的行：
mysql> select * from pendingActivations;
+--------+------------+---------------------+
| userId | code | timestamp |
+--------+------------+---------------------+
| 2 | aaa | 2010-08-23 17:04:02 |
| 2345 | alkfjkla23 | 2010-08-23 16:59:53 |
+--------+------------+---------------------+
插入 userId 等于 2 的行后几分钟，我执行了以下命令，我希望该命令能给我从每行的时间戳。结果如下：
mysql> select userId, code, timestamp, NOW() - timestamp as elapsedSeconds from pendingActivations;
+--------+------------+---------------------+----------------+
| userId | code | timestamp | elapsedSeconds |
+--------+------------+---------------------+----------------+
| 2 | aaa | 2010-08-23 17:04:02 | 136.000000 |
| 2345 | alkfjkla23 | 2010-08-23 16:59:53 | 4585.000000 |
+--------+------------+---------------------+----------------+
我想知道第二行如何具有如此巨大的 elapsedSeconds 值，该值表明恰好过去了 1 小时 16 分 25 秒，尽管很容易看出此后仅过去了大约 5 分钟。
这是表格结构：
mysql> describe pendingActivations;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| userId | int(11) | NO | PRI | NULL | |
| code | varchar(32) | NO | UNI | NULL | |
| timestamp | datetime | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
有什么想法和/或解释吗？
I just tried to insert two rows with the current datetime and then calculated the elapsed time since that date.
Here are the rows from my table after two insertions and using NOW() function to set the timestamp:
mysql> select * from pendingActivations;
+--------+------------+---------------------+
| userId | code | timestamp |
+--------+------------+---------------------+
| 2 | aaa | 2010-08-23 17:04:02 |
| 2345 | alkfjkla23 | 2010-08-23 16:59:53 |
+--------+------------+---------------------+
Few minutes after the insertion of the row with userId equal to 2, I executed the following command which I hoped would give me the elapsed time from the timestamp for each row. Here are the results:
mysql> select userId, code, timestamp, NOW() - timestamp as elapsedSeconds from pendingActivations;
+--------+------------+---------------------+----------------+
| userId | code | timestamp | elapsedSeconds |
+--------+------------+---------------------+----------------+
| 2 | aaa | 2010-08-23 17:04:02 | 136.000000 |
| 2345 | alkfjkla23 | 2010-08-23 16:59:53 | 4585.000000 |
+--------+------------+---------------------+----------------+
I wonder how the second row has that huge elapsedSeconds value which indicates that exactly 1 hour, 16 minutes and 25 seconds had passed, although it is easily seen that just around 5 minutes had passed since.
Here is the table structure:
mysql> describe pendingActivations;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| userId | int(11) | NO | PRI | NULL | |
| code | varchar(32) | NO | UNI | NULL | |
| timestamp | datetime | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
Any ideas and/or explanations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我无法解释这个问题，但我怀疑
-操作以意外的格式返回结果（可能包括微秒或十分之一？）我会使用
TIMEDIFF()。I can't explain the issue, but I suspect the
-operation returns the result in an unexpected format (maybe including microseconds or tenths?)I would use
TIMEDIFF().