如何计算两个日期之间的差异,格式为YYYY-MM-DD hh: mm: ss,并以秒或毫秒为单位得到结果?
当前回答
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获取日期差异(以天为单位)
SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;
+------+
| days |
+------+
| 17 |
+------+
OR
参考下面的链接 MySql的两个时间戳在天之间的差异?
select
unix_timestamp('2007-12-30 00:00:00') -
unix_timestamp('2007-11-30 00:00:00');
为什么不干脆
从表中选择Sum(Date1 - Date2)
Date1和date2是datetime
如果您正在使用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
此代码以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) '
推荐文章
- 前一个月的Python日期
- 将值从同一表中的一列复制到另一列
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- 如何将python datetime转换为字符串,具有可读格式的日期?
- 等价的限制和偏移SQL Server?
- MySQL CPU使用率高
- INT和VARCHAR主键之间有真正的性能差异吗?
- 在c#中创建一个特定时区的DateTime
- 拒绝访问;您需要(至少一个)SUPER特权来执行此操作
- 如何在python中验证日期字符串格式?
- 为什么我不能在DELETE语句中使用别名?
- 在SQL Server Management Studio中保存带有标题的结果
- 从存储引擎得到错误28
- "where 1=1"语句