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?

@didxga: Beware: (end - start) does NOT return a seconds difference between datetime values. It returns a number that is the difference between decimal numbers that look like yyyymmddhhmmss. — helloPiers, Apr 18 '15 at 08:15
Does this answer your question? [How to get the difference between two timestamps in seconds](https://stackoverflow.com/questions/3528219/how-to-get-the-difference-between-two-timestamps-in-seconds) — jdhao, Oct 02 '22 at 15:31

score 401 · Accepted Answer · edited Mar 29 '18 at 20:16

401

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.

edited Mar 29 '18 at 20:16

codeforester

39,467
16
112
140

answered Jan 21 '11 at 15:39

Devid G

4,580
1
16
17

2

What does "the difference in seconds for days" mean exactly? I don't understand why multiplying the result of `TIMEDIFF` by `24*60*60` is not equal to the result of `TIMESTAMPDIFF`. – David Tuite Oct 22 '13 at 09:12
This solution worked for me! But in my case, I´d like to perform the TIMESTAMPDIFF in DAY, but not considering the weekends (sat/sun). I mean, only week days difference... Is it possible in a simple way? If not, I appologize for the inconvenience then I´ll look for another solution. TKs. – Massa Mar 09 '15 at 13:02
8

TIMEDIFF in the example is incorrect as that is not the number of seconds between those two days. TIMEDIFF returns a TIME value, which has hours, minutes and seconds of the difference. Multiplying it will not yield a useful answer. Use TIMESTAMPDIFF. – IvanD May 01 '15 at 06:02
5

Interesting that `TIMEDIFF()` expects the beginning and ending time arguments to be in the opposite order than is expected by `TIMESTAMPDIFF()`. – Mr. Lance E Sloan Jul 31 '17 at 20:12
2

Note: When using TIMEDIFF function, the time value can range from "-838:59:59" to "838:59:59". https://www.w3schools.com/SQl/func_mysql_timediff.asp – cREcker Aug 03 '17 at 12:21
I think there are several ways of solving this; `ROUND(TIMESTAMPDIFF(HOUR, updated_at, NOW()) / (1)) AS 'Date',` gives you the time differences in hours `ROUND(TIMESTAMPDIFF(SECOND, updated_at, NOW()) / (1)) AS 'Date',` gives you the time differences in seconds. `ROUND(TIMESTAMPDIFF(SECOND, updated_at, NOW()) / (60 * 60)) AS 'Date',` gives you the time differences in hours. – Olotin Temitope Sep 01 '20 at 20:44

score 48 · Answer 2 · answered Jan 21 '11 at 13:26

48

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> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60

answered Jan 21 '11 at 13:26

kvista

5,039
1
23
25

7

I dont think this work. `DATEDIFF` doesnt return fractions. – Juan Carlos Oropeza Aug 18 '16 at 15:58

score 44 · Answer 3 · edited May 23 '17 at 12:10

44

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?

edited May 23 '17 at 12:10

Community

1
1

answered Dec 06 '13 at 08:42

Kailas

3,173
5
42
52

dear, its perfect for above question, but i want some modification with same query so could please help me that how can is achive that. here i want to compare end result with my value, like ``SELECT * FROM table where datediff(today,databasedate) as days =3;`` something like this – Sooraj Abbasi Jun 16 '20 at 19:34
@SoorajAbbasi Replying to an old comment, but for reference for anyone in the future: This will only check for if the date was exactly 3 days - you could do `SELECT * FROM table WHERE DATE(database_date) = DATE_SUB(CURDATE(), INTERVAL 3 DAY)` – Edward Oct 20 '22 at 19:39

score 9 · Answer 4 · answered May 15 '13 at 07:10

9

SELECT TIMESTAMPDIFF(HOUR,NOW(),'2013-05-15 10:23:23')
   calculates difference in hour.(for days--> 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')

answered May 15 '13 at 07:10

Romancha KC

1,507
1
13
12

score 4 · Answer 5 · answered Jan 21 '11 at 15:45

4

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

answered Jan 21 '11 at 15:45

ajreal

46,720
11
89
119

3

unix_timestamp - a standard way of timing in the Unix-like systems. Represents a 32-bit integer, indicating how many seconds have passed since 01/01/1970 00:00:00. Ie a lower limit. The upper boundary is limited to 2,106 a year, but due to frequent programs does not operate with this value (instead of an unsigned integer using signed integer) is considered the upper limit in 2038. – Devid G Jan 21 '11 at 16:08
+ when passing through the DST is incorrectly deducted / added – Devid G Jan 21 '11 at 16:09

score 2 · Answer 6 · answered Aug 18 '22 at 12:45

If you want to add where clause with DATEDIFF then it is also possible to add where clause or condition. Take a look of following example.

select DATEDIFF(now(), '2022-08-12 17:55:51.000000') from properties p WHERE p.property_name = 'KEY';

Result : 6

Abdul Rehman Kaim Khani · Answer 7 · 2018-01-21T09:23:16.063

1

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

edited Jan 21 '18 at 09:23

answered Jan 21 '18 at 08:53

Abdul Rehman Kaim Khani

1,062
9
14

Guy · Answer 8 · 2012-06-20T08:12:27.250

0

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

edited Jun 20 '12 at 08:12

answered Jun 19 '12 at 16:57

Guy

1
1

score 0 · Answer 9 · answered Feb 18 '14 at 07:16

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

Why not simply use the `TIMESTAMPDIFF` function? – codeforester Mar 29 '18 at 18:58 — codeforester, Mar 29 '18 at 18:58

score 0 · Answer 10 · answered Dec 14 '16 at 10:03

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.

score 0 · Answer 11 · answered Jan 21 '11 at 13:29

0

Or, you could use TIMEDIFF function

mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
'-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001' , '2008-12-30 01:01:01.000002');
 '46:58:57.999999'

answered Jan 21 '11 at 13:29

Anton Sizikov

9,105
1
28
39

TC wants to get results value in seconds or milliseconds. – Devid G Jan 21 '11 at 15:41

score 0 · Answer 12 · edited Jul 25 '11 at 16:25

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;

score -1 · Answer 13 · answered Jun 27 '13 at 08:52

-1

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

answered Jun 27 '13 at 08:52

didxga

5,935
4
43
58

6

Beware: (end - start) does NOT return a seconds difference between datetime values. It returns a number that is the difference between decimal numbers that look like yyyymmddhhmmss. – helloPiers Apr 18 '15 at 08:15

score -2 · Answer 14 · answered Jul 15 '14 at 12:06

-2

Why not just

Select Sum(Date1 - Date2) from table

date1 and date2 are datetime

answered Jul 15 '14 at 12:06

aikona

1

This doesn't return the date difference in seconds, instead it returns the difference between decimal numbers that look like yyyymmddhhmmss. So, this doesn't solve OP's question. The same thing is mentioned in the comment on the question as well. – codeforester Mar 29 '18 at 18:57

Difference between two dates in MySQL

14 Answers14

Linked

Related