123

Is there a way I can make a query in MySQL that will give me the difference between two timestamps in seconds, or would I need to do that in PHP? And if so, how would I go about doing that?

aknosis
  • 2,764
  • 17
  • 30
The.Anti.9
  • 41,164
  • 46
  • 118
  • 158

4 Answers4

213

You could use the TIMEDIFF() and the TIME_TO_SEC() functions as follows:

SELECT TIME_TO_SEC(TIMEDIFF('2010-08-20 12:01:00', '2010-08-20 12:00:00')) diff;
+------+
| diff |
+------+
|   60 |
+------+
1 row in set (0.00 sec)

You could also use the UNIX_TIMESTAMP() function as @Amber suggested in an other answer:

SELECT UNIX_TIMESTAMP('2010-08-20 12:01:00') - 
       UNIX_TIMESTAMP('2010-08-20 12:00:00') diff;
+------+
| diff |
+------+
|   60 |
+------+
1 row in set (0.00 sec)

If you are using the TIMESTAMP data type, I guess that the UNIX_TIMESTAMP() solution would be slightly faster, since TIMESTAMP values are already stored as an integer representing the number of seconds since the epoch (Source). Quoting the docs:

When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.

Keep in mind that TIMEDIFF() return data type of TIME. TIME values may range from '-838:59:59' to '838:59:59' (roughly 34.96 days)

Community
  • 1
  • 1
Daniel Vassallo
  • 326,724
  • 71
  • 495
  • 436
  • 15
    You can also use [TIMESTAMPDIFF](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_timestampdiff), which does it in a single function - just set the `unit` parameter to `SECOND`. – Mike Aug 20 '10 at 07:33
69

How about "TIMESTAMPDIFF":

SELECT TIMESTAMPDIFF(SECOND,'2009-05-18','2009-07-29') from `post_statistics`

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff

Eaten by a Grue
  • 19,208
  • 10
  • 77
  • 99
David
  • 3,523
  • 28
  • 35
  • If your date column is in another form other that YYYY-MM-DD try doing this: `str_to_date(date_column, '%m/%d/%Y')` within the TIMESTAMPDIFF function for the column that needs it formatting corrected. – greaterKing Jan 24 '17 at 00:08
  • 1
    this is a better answer for me since `TIME_TO_SEC` maxes out at `3020399` whereas this returns the correct value. – Eaten by a Grue Mar 16 '18 at 03:27
  • Mind you that MySQL subtracts the second date from the first, not the inverse. Ugh. – Felipe Zavan Jun 21 '21 at 15:57
  • I don't like this approach because it does not round. I prefer: TIMESTAMPDIFF( SECOND, start_date, end_date ) / 3600.0 – Antonio Andrés Nov 17 '21 at 14:20
  • @AntonioAndrés Ummm... that doesn't round either. "timestampdiff(second, '2009-05-18 07:28:17','2009-07-29 14:23:45') / 3600.0" results in 1734.9244 for example. – Dennis May 02 '22 at 13:19
24
UNIX_TIMESTAMP(ts1) - UNIX_TIMESTAMP(ts2)

If you want an unsigned difference, add an ABS() around the expression.

Alternatively, you can use TIMEDIFF(ts1, ts2) and then convert the time result to seconds with TIME_TO_SEC().

Amber
  • 477,764
  • 81
  • 611
  • 541
17

Note that the TIMEDIFF() solution only works when the datetimes are less than 35 days apart! TIMEDIFF() returns a TIME datatype, and the max value for TIME is 838:59:59 hours (=34,96 days)

fWd82
  • 790
  • 11
  • 30
Power Engineering
  • 694
  • 13
  • 25