How Can I Calculate Date Differences in MySQL Using TIMEDIFF and TIMESTAMPDIFF?

MySQL date difference calculation method

Calculating the difference between two dates is very common in database operations. MySQL provides a variety of functions to simplify this task.

TIMEDIFF and TIMESTAMPDIFF functions

MySQL provides two functions specifically for calculating date differences: TIMEDIFF and TIMESTAMPDIFF.

TIMEDIFF The function returns the difference between two dates in the format of "HH:MM:SS". For example:

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

TIMESTAMPDIFF function returns the difference between two timestamps expressed in a specified time unit (such as seconds, minutes, or days). To get the difference in seconds, use the following syntax:

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

Use TIMESTAMPDIFF to calculate millisecond difference

MySQL itself does not support calculating millisecond differences. However, you can achieve this by multiplying the result of TIMESTAMPDIFF(SECOND) by 1000:

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

Thus, the TIMESTAMPDIFF function provides multiple options for calculating date differences in MySQL, allowing you to adjust the output format and units as needed.

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