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

MySQL date difference calculation

Calculating the time difference between two dates is a common task in data processing and analysis. MySQL provides an easy way to achieve this functionality.

Syntax and return values:

MySQL uses the TIMEDIFF() and TIMESTAMPDIFF() functions to calculate date differences. TIMEDIFF() Returns the difference between two dates, expressed in HH:MM:SS format. TIMESTAMPDIFF() Allows you to specify the time unit for the result, such as seconds or milliseconds.

Usage:

To calculate the second difference, use the following syntax:

SELECT TIMESTAMPDIFF(SECOND, 开始日期, 结束日期);

To get the millisecond difference, use:

SELECT TIMESTAMPDIFF(MICROSECOND, 开始日期, 结束日期) / 1000;

Example:

Suppose you need to calculate the difference between '2007-12-31 10:02:00' and '2007-12-30 12:01:01'.

SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
-- 结果：22:00:59

SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); 
-- 结果：79259

As shown above, TIMESTAMPDIFF() provides the difference in seconds, while TIMEDIFF() displays the difference in hours, minutes, and seconds.

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!