MySQL Date/Time period_diff() Function

The period_diff() is a MySQL date/time function. It is used to get the difference between two given periods.

In this article, we will understand the concept of the MySQL period_diff() function in detail with its various working examples.

Syntax

Parameter:

period1 : period value in format YYMM or YYYYMM.

period2 : period value in format YYMM or YYYYMM.

Returns:

This function returns the difference in months between two periods.

Example 1

Explanation: On executing the above SELECT statement, the period_diff() function is used to return a difference in number of months.

Output: The output of this example is given below.

Example 2

Explanation: On executing the above SELECT statement, the period_diff() function is used to return a difference in number of months.

Output: The output of this example is given below.

Example 3: PERIOD_ADD function with NULL Value as a period values.

Explanation: On executing the above SELECT statement, the period_diff() function returns NULL, if either the value of period or the number is NULL.

Output: The output of this example is given below.

Example 4: PERIOD_DIFF function with Negative Value.

Explanation: On executing the above SELECT statement, the period_diff() function will subtract negative values and return the resulting period.

Output: The output of this example is given below.

Using the MySQL DateTime period_diff() function in a Table column

We can use the period_diff() function in a table to find the difference between the given dates in the specified format YYYYMM or YYMM.

Following the steps given below to implement the MySQL DateTime period_diff() function in a column of the table.

Step 1: First, create a table named Emp_Details using CREATE TABLE statement.

Step 2: After that INSERT the data into a table using the INSERT TABLE statement.

Step 3: To check the information with the use of SELECT statement, whether the data is inserted or not.

Example:

Explanation: On execution of the above SELECT query, the MYSQL period_diff() function in a Emp_Details to find the difference between the Joining_Date and Releiving_Date in the format YYYYMM or YYMM.

Output: The output of this example is given below.

Using the MySQL DateTime period_diff() function with WHERE clause

We can use the period_diff() function in a table to find the difference between the given dates in the specified format YYYYMM or YYMM. By using WHERE clause we can get restricted data from the given column values.

Sample Table: Order_Details

Example:

Explanation: On execution of the above SELECT query, the MYSQL period_diff() function in a Emp_Details to find the difference between the Releiving_Date and the Joining_Date in the format YYYYMM or YYMM where the value of the Salary column is greater than 10000.

Output: The output of this example is given below.

MySQL DateTime period_diff() function with UPDATE Statement

In MySQL, the period_diff() function does not change the data in the table. With the use UPDATE statement we can update the data.

Take an example of the Emp_Details table, which contains the following information.

Syntax:

Example:

Step1: First add a new column i.e. Result to the existing table using the ALTER statement.

Step2: After that use a UPDATE statement to set the value of the Result column.

Explanation: On execution of the above UPDATE query, it will update the records in the Emp_Details table. If you want to check that the content is updated in the table or not, then use the following statement.

Output: After running this query, the output of the table is given below.