MySql difference between two timestamps in days?

Question

How can I get the difference between two timestamps in days? Should I be using a datetime column for this?

I switched my column to datetime. Simple subtraction doesn't seem to give me a result in days.

mysql> SELECT NOW(), last_confirmation_attempt, NOW() - last_confirmation_attempt AS diff  FROM DateClubs HAVING diff IS NOT NULL ;
+---------------------+---------------------------+-----------------+
| NOW()               | last_confirmation_attempt | diff            |
+---------------------+---------------------------+-----------------+
| 2010-03-30 10:52:31 | 2010-03-16 10:41:47       | 14001084.000000 |
+---------------------+---------------------------+-----------------+
1 row in set (0.00 sec)

I don't think diff is in seconds, because when I divide diff by number of seconds in a day ( 86,400 ), I don't get a sensical answer:

mysql> SELECT NOW(), last_confirmation_attempt, ( NOW() - last_confirmation_attempt) / 86400 AS diff  FROM DateClubs HAVING diff IS NOT NULL ;
+---------------------+---------------------------+----------------+
| NOW()               | last_confirmation_attempt | diff           |
+---------------------+---------------------------+----------------+
| 2010-03-30 10:58:58 | 2010-03-16 10:41:47       | 162.0568402778 |
+---------------------+---------------------------+----------------+
1 row in set (0.00 sec)

I was using timestamps, but I just changed one column to datetime. — user151841, Mar 30 '10 at 14:48

score 114 · Accepted Answer · answered Mar 30 '10 at 14:52

114

If you're happy to ignore the time portion in the columns, DATEDIFF() will give you the difference you're looking for in days.

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

answered Mar 30 '10 at 14:52

Uncle Arnie

1,655
1
11
13

8

I was also googling for how to find the difference between the current date, so let it be here: `DATEDIFF(CURDATE(), '2015-04-18')`. – ivkremer Apr 18 '15 at 02:50

score 13 · Answer 2 · answered Mar 23 '12 at 18:03

13

I know is quite old, but I'll say just for the sake of it - I was looking for the same problem and got here, but I needed the difference in days.

I used SELECT (UNIX_TIMESTAMP(DATE1) - UNIX_TIMESTAMP(DATE2))/60/60/24 Unix_timestamp returns the difference in seconds, and then I just divide into minutes(seconds/60), hours(minutes/60), days(hours/24).

answered Mar 23 '12 at 18:03

Enrico

469
3
11

Does this properly handle leap years? – user151841 Mar 26 '12 at 21:14
1

Yes, because the timestamp handles the leap years. The timestamp difference returns the difference between two dates in seconds. If you divide until day, you are fine (every single day every year has the same amount of seconds). – Enrico May 11 '12 at 11:57
1

@Enrico - Not true. Some days have an extra second or two seconds depending on the year. Those occur on either midnight of June 30th or midnight of December 31st. They are leap seconds which adjust for the drift between mean solar day seconds versus the atomic clock second. If you check UNIX and SQL calculators, they will allow for a 62 second minute which means seconds values can range from 0 to 61 inclusive. The adjustment can be negative by two seconds also, but that has never occurred since the drift is always towards needing positive leap seconds. The standard allows for up to 2 seconds. – Jim Jan 28 '15 at 15:48
@Jim I have never had to deal with that. Thanks for the tip! – Enrico Feb 03 '15 at 13:01
@Enrico - another thing to think about is the presence of 23 and 25 hour days in some civil time systems. UTC doesn't ever have that, but North America's and most of Europe's (at the very least) civil time systems also have 23 and 25 hour days whenever clocks are adjusted to or from so-called summer or daylight-savings time. The rules for when a 23 or 25 hour day will occur can be complex. UNIX and SQL time systems have this built in. UTC never observes a 23 or 25 hour day but it still has the aforementioned leap seconds. – Jim Mar 08 '15 at 02:36

Daniel Vassallo · Answer 3 · 2010-03-30T14:55:19.973

CREATE TABLE t (d1 timestamp, d2 timestamp);

INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 05:00:00');
INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-11 00:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-04-01 13:00:00');

SELECT d2, d1, DATEDIFF(d2, d1) AS diff FROM t;

+---------------------+---------------------+------+
| d2                  | d1                  | diff |
+---------------------+---------------------+------+
| 2010-03-30 05:00:00 | 2010-03-11 12:00:00 |   19 |
| 2010-03-30 13:00:00 | 2010-03-11 12:00:00 |   19 |
| 2010-03-30 13:00:00 | 2010-03-11 00:00:00 |   19 |
| 2010-03-30 13:00:00 | 2010-03-10 12:00:00 |   20 |
| 2010-04-01 13:00:00 | 2010-03-10 12:00:00 |   22 |
+---------------------+---------------------+------+
5 rows in set (0.00 sec)

score 7 · Answer 4 · answered Feb 11 '15 at 10:04

7

If you want to return in full TIMESTAMP format than try it: -

 SELECT TIMEDIFF(`call_end_time`, `call_start_time`) as diff from tablename;

return like

     diff
     - - -
    00:05:15

answered Feb 11 '15 at 10:04

Kamlesh Kumar

1,632
2
21
31

score 6 · Answer 5 · edited Jan 12 '19 at 11:36

6

If you need the difference in days accounting up to the second:

SELECT TIMESTAMPDIFF(SECOND,'2010-09-21 21:40:36','2010-10-08 18:23:13')/86400 AS diff

It will return
diff
16.8629

edited Jan 12 '19 at 11:36

JAMSHAID

1,258
9
32

answered Aug 01 '17 at 19:36

Makulit A. Ko

63
1
3

score 3 · Answer 6 · edited Jun 20 '20 at 09:12

3

SELECT DATEDIFF( now(), '2013-06-20' );

here datediff takes two arguments 'upto-date', 'from-date'

What i have done is, using now() function, i can get no. of days since 20-june-2013 till today.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 25 '13 at 17:30

Arvind Kumar

39
1

Amit Bhat · Answer 7 · 2017-12-20T11:39:16.440

0

SELECT DATEDIFF(max_date, min_date) as days from my table. This works even if the col max_date and min_date are in string data types.

edited Dec 20 '17 at 11:39

answered Dec 20 '17 at 09:35

Amit Bhat

13
6

score 0 · Answer 8 · answered Sep 22 '21 at 14:16

Further elaborating upon the answer given by @DanielVassallo and providing an alternate method using TIMESTAMPDIFF() function instead of the DATEDIFF() function used by @DanielVassallo -

CREATE TABLE t (d1 timestamp, d2 timestamp);

INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 05:00:00');
INSERT INTO t VALUES ('2010-03-11 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-11 00:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-03-30 13:00:00');
INSERT INTO t VALUES ('2010-03-10 12:00:00', '2010-04-01 13:00:00');

SELECT d1, d2, TIMESTAMPDIFF(DAY, d1, d2) AS diff FROM t;

+---------------------+---------------------+------+
| d1                  | d2                  | diff |
+---------------------+---------------------+------+
| 2010-03-11 12:00:00 | 2010-03-30 05:00:00 |   18 |
| 2010-03-11 12:00:00 | 2010-03-30 13:00:00 |   19 |
| 2010-03-11 00:00:00 | 2010-03-30 13:00:00 |   19 |
| 2010-03-10 12:00:00 | 2010-03-30 13:00:00 |   20 |
| 2010-03-10 12:00:00 | 2010-04-01 13:00:00 |   22 |
+---------------------+---------------------+------+

MySql difference between two timestamps in days?

8 Answers8

Linked

Related