如何在格式为YYYY-MM-DD hh:mm:ss
的两个日期之间计算差异,并获得以秒或毫秒表示的结果?
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) '
TIMESTAMPDIFF
函数呢? - codeforesterSELECT * 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 (end_time - start_time) FROM t; -- return in Millisecond
SELECT (end_time - start_time)/1000 FROM t; -- return in Second
为什么不直接这样做呢:
从表中选择 Sum(Date1 - Date2)
其中 date1 和 date2 是 datetime 类型