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?
Add a comment
|
4 Answers
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 aTIMESTAMPcolumn, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.Keep in mind that
TIMEDIFF()return data type ofTIME.TIMEvalues may range from '-838:59:59' to '838:59:59' (roughly 34.96 days)
2 Comments
Mike
You can also use TIMESTAMPDIFF, which does it in a single function - just set the
unit parameter to SECOND.
chris
Warning: if you enable
STRICT_TRANS_TABLES then the note that values may range from '-838:59:59'... is particularly important as any use in an UPDATE/INSERT statement which goes beyond this will fail with a Truncated incorrect time value. I would recommend the TIMESTAMPDIFF below.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
5 Comments
greaterKing
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.
You Old Fool
this is a better answer for me since
TIME_TO_SEC maxes out at 3020399 whereas this returns the correct value.
Felipe Zavan
Mind you that MySQL subtracts the second date from the first, not the inverse. Ugh.
Antonio Andrés
I don't like this approach because it does not round. I prefer: TIMESTAMPDIFF( SECOND, start_date, end_date ) / 3600.0
Dennis
@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.
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().
Comments
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)
