使用DATEDIFF获取日期差异(以天为单位)

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

OR

参考下面的链接 MySql的两个时间戳在天之间的差异?

select TO_CHAR(TRUNC(SYSDATE)+(to_date( '31-MAY-2012 12:25', 'DD-MON-YYYY HH24:MI') 
                             - to_date( '31-MAY-2012 10:37', 'DD-MON-YYYY HH24:MI')), 
        'HH24:MI:SS') from dual

——结果:01:48:00

好吧，这不是OP要求的，但这是我想做的:-)

如果您正在使用DATE列(或者可以将它们转换为日期列)，请尝试DATEDIFF()，然后乘以24小时、60分钟、60秒(因为DATEDIFF返回的天数不同)。从MySQL:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

例如:

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60

SELECT TIMESTAMPDIFF(SECOND,'2018-01-19 14:17:15','2018-01-20 14:17:15');

第二种方法

选择(DATEDIFF(1993-02-20)、1993-02-19)

CURRENT_TIME() --this will return current Date
DATEDIFF('','') --this function will return  DAYS and in 1 day there are 24hh 60mm 60sec

select 
unix_timestamp('2007-12-30 00:00:00') - 
unix_timestamp('2007-11-30 00:00:00');

SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
-- result: 22:00:59, the difference in HH:MM:SS format


SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); 
-- result: 79259  the difference in seconds

因此，您可以使用TIMESTAMPDIFF来实现您的目的。