MySQL how to get the difference between two timestamps in seconds

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL: how to get the difference between two timestamps in seconds

The answer is

Examples related to mysql

Examples related to timestamp

Tags