Difference between two dates in MySQL

How to calculate the difference between two dates, in the format 

YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

 Answers

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

So, you can use TIMESTAMPDIFF for your purpose.

---

Get the date difference in days using DATEDIFF

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

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

---

This function takes the difference between two dates and shows it in a date format 

yyyy-mm-dd. All you need is to execute the code below and then use the function. 

After executing you can use it like this

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;

---

This code calculate difference between two dates in yyyy MM dd format.

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 TIMESTAMPDIFF(SECOND,'2018-01-19 14:17:15','2018-01-20 14:17:15');

Second approach

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

---

Why not just

Select Sum(Date1 - Date2) from table

date1 and date2 are datetime