Difference between two dates in MySQL

Question

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?

Answer 1

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 |
+------+

OR

Refer the below link MySql difference between two timestamps in days?

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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;

Answer 6

If you've a date stored in text field as string you can implement this code it will fetch the list of past number of days a week, a month or a year sorting:

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 is your date format

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.

Thank You Hope it will help someone.