MySQL：当减去Date值和NOW（）时有奇怪的结果 [英] MySQL: Strange result when subtracting Date value and NOW()

问题描述

我刚刚尝试用当前的datetime插入两行，然后计算自该日期起的经过时间。
这是两次插入之后的表中的行，并使用NOW（）函数设置时间戳：

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 |
+--------+------------+---------------------+

在userId等于2的行插入后几分钟，我执行了我希望的以下命令会给我每个行的时间戳所花费的时间。结果如下：

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 |
+--------+------------+---------------------+----------------+

我想知道第二个行具有那个巨大的已经过的秒数值，这表明正好1小时16分25秒过去了，尽管很容易看出，从那以后只有大约5分钟。

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.

以下是表格结构：

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    |       | 
+-----------+-------------+------+-----+---------+-------+

任何想法和/或解释？ p>

Any ideas and/or explanations?

推荐答案

我无法解释问题，但我怀疑 - 以非预期的格式（可能包括微秒或十分之一秒）返回结果？

I can't explain the issue, but I suspect the - operation returns the result in an unexpected format (maybe including microseconds or tenths?)

我将使用 TIMEDIFF（） 。

I would use TIMEDIFF().

返回 expr1 - expr2 表示为时间值。 expr1和expr2是时间或日期和时间表达式，但两者必须是相同的类型。

returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

这篇关于MySQL：当减去Date值和NOW（）时有奇怪的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！