132

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
The.Anti.9
  • 43,474
  • 48
  • 123
  • 161

4 Answers4

223

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
  • 337,827
  • 72
  • 505
  • 443
  • 16
    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
73

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

But those new buttons though..
  • 21,377
  • 10
  • 81
  • 108
David
  • 3,843
  • 33
  • 36
  • 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. – But those new buttons though.. 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
  • 507,862
  • 82
  • 626
  • 550
18

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
  • 840
  • 1
  • 13
  • 31
Power Engineering
  • 713
  • 14
  • 26