MySQL中两个日期之间的差异

如果您正在使用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

或者，您可以使用TIMEDIFF函数

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
'-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001' , '2008-12-30 01:01:01.000002');
'46:58:57.999999'

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来实现你的目的。

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

该函数获取两个日期的差值，并以日期格式yyyy-mm-dd显示。您只需要执行下面的代码，然后使用该函数。执行后，您可以像这样使用它

SELECT datedifference(date1, date2)
FROM ....
.
.
.
.




DELIMITER $$


CREATE FUNCTION datedifference(date1 DATE, date2 DATE) RETURNS DATE
NO SQL


BEGIN
DECLARE dif DATE;
IF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < 0    THEN
SET dif=DATE_FORMAT(
CONCAT(
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 ,
'-',
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 ,
'-',
DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(DATE_SUB(date1, INTERVAL 1 MONTH)), '-', DAY(date2))))),
'%Y-%m-%d');
ELSEIF DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2)))) < DAY(LAST_DAY(DATE_SUB(date1, INTERVAL 1 MONTH))) THEN
SET dif=DATE_FORMAT(
CONCAT(
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 ,
'-',
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 ,
'-',
DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
'%Y-%m-%d');
ELSE
SET dif=DATE_FORMAT(
CONCAT(
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))DIV 12 ,
'-',
PERIOD_DIFF(date_format(date1, '%y%m'),date_format(date2, '%y%m'))% 12 ,
'-',
DATEDIFF(date1, DATE(CONCAT(YEAR(date1),'-', MONTH(date1), '-', DAY(date2))))),
'%Y-%m-%d');
END IF;


RETURN dif;
END $$
DELIMITER;

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')

你可以简单地这样做:

SELECT (end_time - start_time) FROM t; -- return in Millisecond
SELECT (end_time - start_time)/1000 FROM t; -- return in Second

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

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

或

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

此代码以yyyy MM dd格式计算两个日期之间的差值。

declare @StartDate datetime
declare @EndDate datetime


declare @years int
declare @months int
declare @days int


--NOTE: date of birth must be smaller than As on date,
--else it could produce wrong results
set @StartDate = '2013-12-30' --birthdate
set @EndDate  = Getdate()            --current datetime


--calculate years
select @years = datediff(year,@StartDate,@EndDate)


--calculate months if it's value is negative then it
--indicates after __ months; __ years will be complete
--To resolve this, we have taken a flag @MonthOverflow...
declare @monthOverflow int
select @monthOverflow = case when datediff(month,@StartDate,@EndDate) -
( datediff(year,@StartDate,@EndDate) * 12) <0 then -1 else 1 end
--decrease year by 1 if months are Overflowed
select @Years = case when @monthOverflow < 0 then @years-1 else @years end
select @months =  datediff(month,@StartDate,@EndDate) - (@years * 12)


--as we do for month overflow criteria for days and hours
--& minutes logic will followed same way
declare @LastdayOfMonth int
select @LastdayOfMonth =  datepart(d,DATEADD
(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate)+1,0)))


select @days = case when @monthOverflow<0 and
DAY(@StartDate)> DAY(@EndDate)
then @LastdayOfMonth +
(datepart(d,@EndDate) - datepart(d,@StartDate) ) - 1
else datepart(d,@EndDate) - datepart(d,@StartDate) end




select
@Months=case when @days < 0 or DAY(@StartDate)> DAY(@EndDate) then @Months-1 else @Months end


Declare @lastdayAsOnDate int;
set @lastdayAsOnDate = datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDate),0)));
Declare @lastdayBirthdate int;
set @lastdayBirthdate =  datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@StartDate)+1,0)));


if (@Days < 0)
(
select @Days = case when( @lastdayBirthdate > @lastdayAsOnDate) then
@lastdayBirthdate + @Days
else
@lastdayAsOnDate + @Days
end
)
print  convert(varchar,@years)   + ' year(s),   '  +
convert(varchar,@months)  + ' month(s),   ' +
convert(varchar,@days)    + ' day(s)   '

为什么不干脆

从表中选择Sum(Date1 - Date2)

Date1和date2是datetime

如果你在文本字段中存储了一个日期作为字符串，你可以实现这段代码，它将获取过去一周，一个月或一年排序的天数列表:

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是日期格式

这个查询显示了你在那里设置的天数之间的记录，比如:从过去7天开始的以下记录和从过去14天开始的以上记录，所以它将是你要显示的最后一周记录，同样的概念是月或年。无论你在日期下面提供什么值，比如:从7天开始，那么另一个值将是14天的两倍。我们在这里所说的是过去14天以上和过去7天以下的所有记录。这是一个星期记录，您可以将值更改为一个月的30-60天，也可以更改为一年。

希望它能帮助到别人。

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

第二种方法

SELECT ( DATEDIFF('1993-02-20','1993-02-19')*( 24*60*60) )AS 'seccond';

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

select DATEDIFF(now(), '2022-08-12 17:55:51.000000') from properties p WHERE p.property_name = 'KEY';

结果:6