How to Calculate the Time Difference Between Two Timestamps in MySQL?

Getting the Time Difference between Two Timestamps in MySQL

In MySQL, you can calculate the difference between two timestamps in seconds using the TIMEDIFF() and TIME_TO_SEC() functions:

SELECT TIME_TO_SEC(TIMEDIFF('2010-08-20 12:01:00', '2010-08-20 12:00:00')) AS diff;

This will return a value of 60, indicating a one-minute difference between the two timestamps.

Alternatively, you can use the UNIX_TIMESTAMP() function to calculate the difference directly:

SELECT UNIX_TIMESTAMP('2010-08-20 12:01:00') - UNIX_TIMESTAMP('2010-08-20 12:00:00') AS diff;

This will also return a value of 60.

Considerations for Using TIMEDIFF() and UNIX_TIMESTAMP():

• If the time difference exceeds the range of TIME values (-838:59:59 to 838:59:59), TIMEDIFF() will return NULL.
• UNIX_TIMESTAMP() is typically faster for calculating time differences with TIMESTAMP columns because it directly operates on the internal integer representation of the timestamp.

The above is the detailed content of How to Calculate the Time Difference Between Two Timestamps in MySQL?. For more information, please follow other related articles on the PHP Chinese website!