如何计算两个日期之间的差异，格式为YYYY-MM-DD hh: mm: ss，并以秒或毫秒为单位得到结果?
如果您正在使用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 |
+------+
OR
参考下面的链接 MySql的两个时间戳在天之间的差异?
此代码以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) '
如果你在文本字段中存储了一个日期作为字符串，你可以实现这段代码，它将获取过去一周，一个月或一年排序的天数列表:
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
如果你想添加带有DATEDIFF的where子句，那么也可以添加where子句或条件。 请看下面的例子。
select DATEDIFF(now(), '2022-08-12 17:55:51.000000') from properties p WHERE p.property_name = 'KEY';
结果:6
