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