MySql在两个时间戳之间的差异？ [英] MySql difference between two timestamps in days?

问题描述

如何获取两个时间戳之间的差异？我应该为此使用datetime列吗？

---

我将列转换为datetime。简单的减法似乎并没有给我几天的结果。

How can I get the difference between two timestamps in days? Should I be using a datetime column for this?

---

I switched my column to datetime. Simple subtraction doesn't seem to give me a result in days.

mysql> SELECT NOW(), last_confirmation_attempt, NOW() - last_confirmation_attempt AS diff  FROM DateClubs HAVING diff IS NOT NULL ;
+---------------------+---------------------------+-----------------+
| NOW()               | last_confirmation_attempt | diff            |
+---------------------+---------------------------+-----------------+
| 2010-03-30 10:52:31 | 2010-03-16 10:41:47       | 14001084.000000 |
+---------------------+---------------------------+-----------------+
1 row in set (0.00 sec)

我不认为 diff 是在几秒钟内，因为当我划分 diff 一天中的秒数（86,400），我没有得到一个敏感的答案：

I don't think diff is in seconds, because when I divide diff by number of seconds in a day ( 86,400 ), I don't get a sensical answer:

mysql> SELECT NOW(), last_confirmation_attempt, ( NOW() - last_confirmation_attempt) / 86400 AS diff  FROM DateClubs HAVING diff IS NOT NULL ;
+---------------------+---------------------------+----------------+
| NOW()               | last_confirmation_attempt | diff           |
+---------------------+---------------------------+----------------+
| 2010-03-30 10:58:58 | 2010-03-16 10:41:47       | 162.0568402778 |
+---------------------+---------------------------+----------------+
1 row in set (0.00 sec)

推荐答案

如果您乐意忽略列中的时间部分， DATEDIFF（）将让您在几天内寻找差异。 p>

If you're happy to ignore the time portion in the columns, DATEDIFF() will give you the difference you're looking for in days.

SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
+------+
| days |
+------+
|   17 |
+------+

这篇关于MySql在两个时间戳之间的差异？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！