MySQL PERIOD_DIFF() function

PERIOD_DIFF() function

MySQL PERIOD_DIFF() returns the difference between two periods. Periods should be in the same format i.e. YYYYMM or YYMM. It is to be noted that periods are not date values. This function is particularly useful when dealing with data that involves comparing or calculating differences between year-month intervals.

This function is useful in -

• PERIOD_DIFF() is valuable for calculating the time interval between two periods, represented in year-month format.
• The function is used for time-based analysis, such as measuring the duration between two specific time points.
• PERIOD_DIFF() is used to transform period-based data into meaningful insights about time intervals.
• The function is valuable for calculating billing cycles, subscription periods, and contract terms.
• PERIOD_DIFF() can be used to generate custom reports that involve comparing period intervals.
• PERIOD_DIFF() aids in financial projections and forecasting by determining the number of months between different periods.
• The function supports analyzing the relative time between two periods, helping to identify the chronological relationship.

Syntax:

PERIOD_DIFF(P1,P2);

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL PERIOD_DIFF() function

The following statement will return a value in YYYYMM or YYMM format after calculating the difference between two periods 200905 and 200811.

Code:

SELECT PERIOD_DIFF(200905,200811);

Output:

mysql> SELECT PERIOD_DIFF(200905,200811);
+----------------------------+
| PERIOD_DIFF(200905,200811) |
+----------------------------+
|                          6 | 
+----------------------------+
1 row in set (0.00 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: PERIOD_ADD()
Next: QUARTER()