MySQL Date calculation
Date calculation
In this page, we have shown an advanced MySQL date calculation using the following functions :
CURDATE() function which returns the current date of the computer, YEAR() function which returns the year of the specified date, MONTH() function which returns the month of the specified date, DAY() function which returns the day of the specified date, RIGHT() function which returns the number of character as specified within the function from the given string or date. The part of the expression that compares the returns from RIGHT() function evaluates 1 or 0.
Example:
Sample table: publisher
Code:
SELECT pub_name, estd, CURDATE(),
YEAR(estd),MONTH(estd),
DAY(estd),(RIGHT(CURDATE(),5)<RIGHT(estd,5)) AS ‘return’
FROM publisher;
Explanation:
The above MySQL statement returns Publisher's name, Date of establishment, Current date, Year of establishment, Month of establishment, Day of establishment and return column which is calculated using RIGHT().
Sample Output:
mysql> SELECT pub_name, estd, CURDATE(),
-> YEAR(estd),MONTH(estd),
-> DAY(estd),(RIGHT(CURDATE(),5)<RIGHT(estd,5)) AS "return"
-> FROM publisher;
+------------------------------+------------+------------+------------+-------------+-----------+--------+
| pub_name | estd | CURDATE() | YEAR(estd) | MONTH(estd) | DAY(estd) | return |
+------------------------------+------------+------------+------------+-------------+-----------+--------+
| Jex Max Publication | 1969-12-25 | 2015-04-04 | 1969 | 12 | 25 | 1 |
| BPP Publication | 1985-10-01 | 2015-04-04 | 1985 | 10 | 1 | 1 |
| New Harrold Publication | 1975-09-05 | 2015-04-04 | 1975 | 9 | 5 | 1 |
| Ultra Press Inc. | 1948-07-10 | 2015-04-04 | 1948 | 7 | 10 | 1 |
| Mountain Publication | 1975-01-01 | 2015-04-04 | 1975 | 1 | 1 | 0 |
| Summer Night Publication | 1990-12-10 | 2015-04-04 | 1990 | 12 | 10 | 1 |
| Pieterson Grp. of Publishers | 1950-07-15 | 2015-04-04 | 1950 | 7 | 15 | 1 |
| Novel Publisher Ltd. | 2000-01-01 | 2015-04-04 | 2000 | 1 | 1 | 0 |
+------------------------------+------------+------------+------------+-------------+-----------+--------+
8 rows in set (0.00 sec)
Note: Since CURDATE() function is used, your output may vary from the output shown.
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-date-calculation - php mysql examples | w3resource</title>
<meta name="description" content="example-date-calculation - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of publishers with date , year and month of establishment of the concerned publishing house, current date and
and value against the expression RIGHT(CURDATE(),5) < RIGHT(estd,5) as 'return':</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's name</th><th>Date of establishment</th><th>Current date</th><th>Year of establishment</th><th>Month of establishment</th><th>Day of establishment</th><th>return</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name, estd, CURDATE(),YEAR(estd),MONTH(estd),DAY(estd),(RIGHT(CURDATE(),5)<RIGHT(estd,5))
AS "return" FROM publisher') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "<td>" . $row['CURDATE()'] . "</td>";
echo "<td>" . $row['YEAR(estd)'] . "</td>";
echo "<td>" . $row['MONTH(estd)'] . "</td>";
echo "<td>" . $row['DAY(estd)'] . "</td>";
echo "<td>" . $row['return'] . "</td>";
echo "<td>" . $row['return'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
MySQL Date subtraction
Date subtraction
In the following we have discussed advanced MySQL date subtraction using CURDATE(), YEAR() and RIGHT() functions.
Example:
Sample table: publisher
Code:
SELECT pub_name, estd,CURDATE(),
(YEAR(CURDATE())-YEAR(estd)) -
(RIGHT(CURDATE(),5)<RIGHT(estd,5))
AS Age FROM publisher;
Explanation:
To calculate how old each publisher is, the difference of dates between the current date and the estd date is calculated, then 1 (evaluated by (RIGHT(CURDATE(),5)<RIGHT(estd,5))) is subtracted if the current month and day are earlier than the estd month and day.
Sample Output:
mysql> SELECT pub_name, estd,CURDATE(),
-> (YEAR(CURDATE())-YEAR(estd)) -
-> (RIGHT(CURDATE(),5)<RIGHT(estd,5))
-> AS Age FROM publisher;
+------------------------------+------------+------------+------+
| pub_name | estd | CURDATE() | Age |
+------------------------------+------------+------------+------+
| Jex Max Publication | 1969-12-25 | 2015-04-01 | 45 |
| BPP Publication | 1985-10-01 | 2015-04-01 | 29 |
| New Harrold Publication | 1975-09-05 | 2015-04-01 | 39 |
| Ultra Press Inc. | 1948-07-10 | 2015-04-01 | 66 |
| Mountain Publication | 1975-01-01 | 2015-04-01 | 40 |
| Summer Night Publication | 1990-12-10 | 2015-04-01 | 24 |
| Pieterson Grp. of Publishers | 1950-07-15 | 2015-04-01 | 64 |
| Novel Publisher Ltd. | 2000-01-01 | 2015-04-01 | 15 |
+------------------------------+------------+------------+------+
8 rows in set (0.00 sec)
Note: Since Current date is one of the aspects to achieve the output, your output and output from the PHP script will differ from the above output.
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-date-subtraction - php mysql examples | w3resource</title>
<meta name="description" content="example-date-subtraction - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of publishers with date of establishment, current date of establishment and how old their publishing houses are:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's name</th><th>Date of establishment</th><th>Current date</th><th>AGE</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name, estd, CURDATE(),(YEAR(CURDATE())-YEAR(estd)) - (RIGHT(CURDATE(),5)<RIGHT(estd,5))
AS Age FROM publisher') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "<td>" . $row['CURDATE()'] . "</td>";
echo "<td>" . $row['Age'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
MySQL date calculation with mod()
Date calculation with mod()
In the following, we have discussed how to use MySQL MOD() function to calculate the date.
Example:
Sample table: publisher
Code:
SELECT pub_name,estd
FROM publisher
WHERE MONTH(estd) = MOD(MONTH(CURDATE()),12)+8;
Explanation:
The MOD() function returns the modules from a division.
The above MySQL statement returns pub_name and estd of those publishers, whose month of an establishment is equal to the addition of 8 and modulus of the month part of the current date divided by 12.
Sample Output:
mysql> SELECT pub_name,estd
-> FROM publisher
-> WHERE MONTH(estd) = MOD(MONTH(CURDATE()),12)+8;
+--------------------------+------------+
| pub_name | estd |
+--------------------------+------------+
| Jex Max Publication | 1969-12-25 |
| Summer Night Publication | 1990-12-10 |
+--------------------------+------------+
2 rows in set (0.00 sec)
Note: Since the current date is one of the aspects to achieve the output, your output and output from the PHP script may differ from the above output. Change the current date to a suitable date (for example before August 2010) to get an output.
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-mod - php mysql example | w3resource</title>
<meta name="description" content="example-mod - php mysql example | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of publishers with their date of establishment, where their respective month of establishment equals,
addition of one to the reminder of the current month divided by twelve:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher's name</th><th>Date of establishment</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name,estd FROM publisher WHERE MONTH(estd) = MOD(MONTH(CURDATE()),12)+8') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['estd'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
Note: The select statement may return zero rows. This is only because the data available in the table agsinst which the query is executed, does not have a date present with respect to the current date. Otherwise, the query is technically correct.
Online Practice Editor:
Previous: Rows holding group wise maximum for a column
Next: Date Calculation using order by
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics