使用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要求的，但这是我想做的:-)

SELECT TIMESTAMPDIFF(HOUR,NOW(),'2013-05-15 10:23:23')
   calculates difference in hour.(for days--> you have to define day replacing hour
SELECT DATEDIFF('2012-2-2','2012-2-1')

SELECT TO_DAYS ('2012-2-2')-TO_DAYS('2012-2-1')

如果您正在使用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 * FROM `table` WHERE STR_TO_DATE(mydate, '%d/%m/%Y') < CURDATE() - INTERVAL 30 DAY AND STR_TO_DATE(date, '%d/%m/%Y') > CURDATE() - INTERVAL 60 DAY

//This is for a month

SELECT * FROM `table` WHERE STR_TO_DATE(mydate, '%d/%m/%Y') < CURDATE() - INTERVAL 7 DAY AND STR_TO_DATE(date, '%d/%m/%Y') > CURDATE() - INTERVAL 14 DAY

//This is for a week 

%d%m%Y是日期格式

This query display the record between the days you set there like: Below from last 7 days and Above from last 14 days so it would be your last week record to be display same concept is for month or year. Whatever value you're providing in below date like: below from 7-days so the other value would be its double as 14 days. What we are saying here get all records above from last 14 days and below from last 7 days. This is a week record you can change value to 30-60 days for a month and also for a year.

希望它能帮助到别人。

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