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

User · Answer

select  unix timestamp  2007-12-30 00 00 00   -  unix timestamp  2007-11-30 00 00 00

User · Answer

Why not just   Select Sum Date1 - Date2  from table   date1 and date2 are datetime

User · Answer

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

User · Answer

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

User · Answer

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      -- result   01 48 00   OK it s not quite what the OP asked  but it s what I wanted to do  -

User · Answer

Or  you could use TIMEDIFF function   mysql gt  SELECT TIMEDIFF  2000 01 01 00 00 00    2000 01 01 00 00 00 000001     -00 00 00 000001  mysql gt  SELECT TIMEDIFF  2008-12-31 23 59 59 000001     2008-12-30 01 01 01 000002      46 58 57 999999

User · Answer

SELECT TIMESTAMPDIFF HOUR NOW    2013-05-15 10 23 23      calculates difference in hour  for days-- gt  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

User · Answer

If you are working with DATE columns  or can cast them as date columns   try DATEDIFF   and then multiply by 24 hours  60 min  60 secs  since DATEDIFF returns diff in days    From MySQL   http   dev mysql com doc refman 5 5 en date-and-time-functions html  for example   mysql gt  SELECT DATEDIFF  2007-12-31 23 59 59   2007-12-30 00 00 00     24 60 60

User · Answer

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

User · Answer

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   lt 0 then -1 else 1 end --decrease year by 1 if months are Overflowed select  Years   case when  monthOverflow  lt  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  -- amp  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 lt 0 and      DAY  StartDate  gt  DAY  EndDate   then  LastdayOfMonth       datepart d  EndDate  - datepart d  StartDate    - 1         else datepart d  EndDate  - datepart d  StartDate  end    select   Months case when  days  lt  0 or DAY  StartDate  gt  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  lt  0         select  Days   case when   lastdayBirthdate  gt   lastdayAsOnDate  then          lastdayBirthdate    Days     else          lastdayAsOnDate    Days     end   print  convert varchar  years        year s                 convert varchar  months       month s                convert varchar  days         day s

User · Answer

You would simply do this   SELECT  end time - start time  FROM t  -- return in Millisecond SELECT  end time - start time  1000 FROM t  -- return in Second

User · Answer

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

User · Answer

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    lt  CURDATE   - INTERVAL 30 DAY AND STR TO DATE date    d  m  Y    gt  CURDATE   - INTERVAL 60 DAY    This is for a month  SELECT   FROM  table  WHERE STR TO DATE mydate    d  m  Y    lt  CURDATE   - INTERVAL 7 DAY AND STR TO DATE date    d  m  Y    gt  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

[mysql] Difference between two dates in MySQL

Examples related to mysql

Examples related to sql

Examples related to date

Examples related to datetime