MySql 两个时间戳之间的天数差异？

MySql difference between two timestamps in days?

提问by user151841

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)

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

我认为diff不是以秒为单位，因为当我除以diff一天中的秒数（ 86,400 ）时，我没有得到合理的答案：

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)

回答by Uncle Arnie

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

如果您乐于忽略列中的时间部分，则DATEDIFF()将为您提供所需的天数差异。

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

回答by Enrico

I know is quite old, but I'll say just for the sake of it - I was looking for the same problem and got here, but I needed the difference in days.

我知道它已经很老了，但我只是为了它而说 - 我一直在寻找同样的问题并来到这里，但我需要几天的差异。

I used SELECT (UNIX_TIMESTAMP(DATE1) - UNIX_TIMESTAMP(DATE2))/60/60/24Unix_timestamp returns the difference in seconds, and then I just divide into minutes(seconds/60), hours(minutes/60), days(hours/24).

我使用SELECT (UNIX_TIMESTAMP(DATE1) - UNIX_TIMESTAMP(DATE2))/60/60/24Unix_timestamp返回以秒为单位的差异，然后我只是分为分钟（秒/ 60），小时（分钟/ 60），天（小时/ 24）。

回答by Kamlesh

If you want to return in full TIMESTAMP format than try it: -

如果您想以完整的 TIMESTAMP 格式返回，请尝试：-

 SELECT TIMEDIFF(`call_end_time`, `call_start_time`) as diff from tablename;

return like

返回喜欢

     diff
     - - -
    00:05:15

回答by Daniel Vassallo

CREATE TABLE t (d1 timestamp, d2 timestamp);

INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 05:00:00');
INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-11 00:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-04-01 13:00:00');

SELECT d2, d1, DATEDIFF(d2, d1) AS diff FROM t;

+---------------------+---------------------+------+
| d2                  | d1                  | diff |
+---------------------+---------------------+------+
| 2010-03-30 05:00:00 | 2010-03-11 12:00:00 |   19 |
| 2010-03-30 13:00:00 | 2010-03-11 12:00:00 |   19 |
| 2010-03-30 13:00:00 | 2010-03-11 00:00:00 |   19 |
| 2010-03-30 13:00:00 | 2010-03-10 12:00:00 |   20 |
| 2010-04-01 13:00:00 | 2010-03-10 12:00:00 |   22 |
+---------------------+---------------------+------+
5 rows in set (0.00 sec)

回答by Makulit A. Ko

If you need the difference in days accounting up to the second:

如果您需要计算到第二个的天数差异：

SELECT TIMESTAMPDIFF(SECOND,'2010-09-21 21:40:36','2010-10-08 18:23:13')/86400 AS diff

It will return
diff
16.8629

它会回来
diff
16.8629

回答by Arvind Kumar

SELECT DATEDIFF( now(), '2013-06-20' );

SELECT DATEDIFF( now(), '2013-06-20' );

here datediff takes two arguments 'upto-date', 'from-date'

What i have done is, using now() function, i can get no. of days since 20-june-2013 till today.

这里 datediff 需要两个参数“最新”，“从日期”

我所做的是，使用 now() 函数，我可以得到没有。2013 年 6 月 20 日至今的天数。

回答by Amit Bhat

SELECT DATEDIFF(max_date, min_date) as days from my table. This works even if the col max_dateand min_dateare in string data types.

SELECT DATEDIFF(max_date, min_date) 作为我表中的天数。即使 colmax_date和min_date是字符串数据类型，这也有效。