How to calculate the number of days between two dates in MySQL

MySQL calculates the number of days between two dates

In MySQL 5.7, calculating the number of days between two dates is a common task.

1. DATEDIFF function

• DATEDIFFfunction:
• The difference in the number of days between two dates can be directly calculated.

-- calculate2024Year1moon1Rising2024Year1moon10The difference in days between days
SELECT DATEDIFF('2024-01-10', '2024-01-01') AS days_difference; # result 9，Indicates the difference between two dates9sky。

2. TIMESTAMPDIFF function

• TIMESTAMPDIFFfunction:
• Allows you to specify date and time units to calculate the difference, here we useDAYAs a unit.

-- calculate2024Year1moon1Rising2024Year1moon10The difference in days between days
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-10') AS days_difference; # result 9

3. PERIOD_DIFF function

• PERIOD_DIFFfunction:
• Specifically used to calculate the between two datesmonthdifference.

-- calculate2024Year1Yuehe2024Year12Month differences between months
SELECT PERIOD_DIFF(202412, 202401) AS months_difference; # result -11

It means that the difference is 11 months from January 2024 to December 2024 (note that the result is a negative number because the second date is after the first date).

4. Function comparison

Here is a table, compareDATEDIFF、TIMESTAMPDIFFandPERIOD_DIFF

The difference between these three functions:

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.