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

问题描述

如何以天为单位获得两个时间戳之间的差异?我应该为此使用日期时间列吗?<人力资源>我将我的专栏切换到日期时间.简单的减法似乎不会在几天内给我结果.

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() 将在几天内为您提供您正在寻找的差异.

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屋！