How to Calculate Date Differences in MySQL?

MySQL date difference calculation method

When operating dates in a MySQL database, you often need to calculate the difference between two dates. This is useful in tasks such as calculating time intervals or ages. This article explains how to calculate the difference between two dates in MySQL.

Use TIMESTAMPDIFF function

TIMESTAMPDIFF function can calculate the difference between two timestamps within the specified time unit. Its syntax is as follows:

<code class="language-sql">SELECT TIMESTAMPDIFF(unit, timestamp_expression1, timestamp_expression2);</code>

Among them:

• unit is the unit of time in which the difference is to be returned, such as YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.
• timestamp_expression1 and timestamp_expression2 are the two timestamps whose difference is to be calculated.

For example, to calculate the difference in seconds between two timestamps, you would use:

<code class="language-sql">SELECT TIMESTAMPDIFF(SECOND, '2023-01-01 12:00:00', '2023-02-01 14:00:00');</code>

The result will be 2,592,000 seconds, representing the difference between the two timestamps.

Use TIMEDIFF function

The TIMEDIFF function provides another way to calculate the difference between two timestamps. Unlike TIMESTAMPDIFF, it only returns the difference in hours, minutes, and seconds in HH:MM:SS format. Its syntax is as follows:

<code class="language-sql">SELECT TIMEDIFF(timestamp_expression1, timestamp_expression2);</code>

For example:

<code class="language-sql">SELECT TIMEDIFF('2023-01-01 12:00:00', '2023-02-01 14:00:00');</code>

The result will be '-02:00:00', representing the difference in hours, minutes and seconds between the two timestamps. Note that the result may be negative, depending on the order of the timestamps.

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