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.




Name Description
P1 Indicates a period.
P2 Indicates a period.

Syntax Diagram:

MySQL PERIOD_DIFF() Function - Syntax Diagram

MySQL Version: 5.6

Video Presentation:

Pictorial Presentation:

Pictorial Presentation of MySQL PERIOD_DIFF() function

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.


SELECT PERIOD_DIFF(200905,200811);

Sample Output:

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

PHP script:

<!doctype html>
<html lang="en">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-PERIOD_DIFF-function - php mysql examples | w3resource</title>
<meta name="description" content="example-PERIOD_DIFF-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Calculating difference between May 2009 and November 2008 :</h2>
<table class='table table-bordered'>
<th>Calculated difference</th>
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT PERIOD_DIFF(200905,200811)') as $row) {
echo "<tr>";
echo "<td>" . $row['PERIOD_DIFF(200905,200811)'] . "</td>";
echo "</tr>";

View the example in browser

JSP script:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
try {
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT PERIOD_DIFF(200905,200811)";
rs = statement.executeQuery(Data);
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Calculated difference</td>
while (rs.next()) {
<%   }    %>
} catch (Exception ex) {
out.println("Can’t connect to database.");

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: PERIOD_ADD()

Share this Tutorial / Exercise on : Facebook and Twitter