问题描述

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

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

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 两个时间戳之间的天数差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！