How to Calculate Date and Time Differences in MySQL?

MySQL date and time difference calculation method

In database programming, calculating the difference between two date times is a very common operation. MySQL provides two main ways to achieve this functionality: TIMEDIFF and TIMESTAMPDIFF.

TIMEDIFF function

TIMEDIFF The function calculates the difference between two datetime values ​​and returns the result in HH:MM:SS format. For example:

<code class="language-sql">SELECT TIMEDIFF('2007-12-31 10:02:00', '2007-12-30 12:01:01');
-- 结果：22:00:59</code>

This query returns the difference between two dates, which is 22 hours, 0 minutes and 59 seconds.

TIMESTAMPDIFF function

TIMESTAMPDIFF The function calculates the difference between two dates, times, or timestamps and returns an integer representing the number of seconds or milliseconds. For example:

<code class="language-sql">SELECT TIMESTAMPDIFF(SECOND, '2007-12-30 12:01:01', '2007-12-31 10:02:00');
-- 结果：79259</code>

This query returns the difference in seconds between two dates, which is 79259 seconds.

How to use

Which method to choose depends on the desired output format. If you want the difference in HH:MM:SS format, use TIMEDIFF; if you want the difference in seconds or milliseconds, use TIMESTAMPDIFF.

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