w3resource
MYSQL Tutorial

MySQL Date calculation using order by

Date calculation using order by

In this page, we have discussed how to perform MySQL date calculation using ORDER BY.

Example:

Sample table: publisher


Code:

SELECT pub_name, estd, CURDATE(),
(YEAR(CURDATE())-YEAR(estd))
- (RIGHT(CURDATE(),5)<RIGHT(estd,5))
AS Age
FROM publisher
ORDER BY pub_name;

Explanation

It will be easy to examine the age of the publisher because an ORDER BY clause have been added which will sort the publisher in ascending order.

Sample Output:

mysql> SELECT pub_name, estd, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(estd))
    -> - (RIGHT(CURDATE(),5)>RIGHT(estd,5))
    -> AS Age
    -> FROM publisher
    -> ORDER BY pub_name;
+------------------------------+------------+------------+------+
| pub_name                     | estd       | CURDATE()  | Age  |
+------------------------------+------------+------------+------+
| BPP Publication              | 1985-10-01 | 2015-04-01 |   29 | 
| Jex Max Publication          | 1969-12-25 | 2015-04-01 |   45 | 
| Mountain Publication         | 1975-01-01 | 2015-04-01 |   40 | 
| New Harrold Publication      | 1975-09-05 | 2015-04-01 |   39 | 
| Novel Publisher Ltd.         | 2000-01-01 | 2015-04-01 |   15 | 
| Pieterson Grp. of Publishers | 1950-07-15 | 2015-04-01 |   64 | 
| Summer Night Publication     | 1990-12-10 | 2015-04-01 |   24 | 
| Ultra Press Inc.             | 1948-07-10 | 2015-04-01 |   66 | 
+------------------------------+------------+------------+------+
8 rows in set (0.02 sec)

Note: Since 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.

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-date-calculation-using-order-by - php mysql examples | w3resource</title>
<meta name="description" content="example-date-calculation-using-order-by - 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. The list is sorted against the name of the publisher:</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
ORDER BY pub_name') 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>

View the example in browser

MySQL Date calculation with order by descending order

Date calculation

In the following we have discussed how to sort the column after performing MySQL date calculation by descending order.

Example:

Sample table: publisher


Code:

SELECT pub_name, estd, CURDATE(), 
(YEAR(CURDATE())-YEAR(estd))- 
(RIGHT(CURDATE(),5)<RIGHT(estd,5)) AS Age 
FROM publisher ORDER BY Age DESC;

Explanation

The above MySQL statement is used to sort the output by Age in descending order.

Sample Output:

mysql> SELECT pub_name, estd, CURDATE(), 
    -> (YEAR(CURDATE())-YEAR(estd))- 
    -> (RIGHT(CURDATE(),5)<RIGHT(estd,5)) AS Age 
    -> FROM publisher ORDER BY Age DESC;
+------------------------------+------------+------------+------+
| pub_name                     | estd       | CURDATE()  | Age  |
+------------------------------+------------+------------+------+
| Ultra Press Inc.             | 1948-07-10 | 2015-04-01 |   66 | 
| Pieterson Grp. of Publishers | 1950-07-15 | 2015-04-01 |   64 | 
| Jex Max Publication          | 1969-12-25 | 2015-04-01 |   45 | 
| Mountain Publication         | 1975-01-01 | 2015-04-01 |   40 | 
| New Harrold Publication      | 1975-09-05 | 2015-04-01 |   39 | 
| BPP Publication              | 1985-10-01 | 2015-04-01 |   29 | 
| Summer Night Publication     | 1990-12-10 | 2015-04-01 |   24 | 
| 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 may 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">
<title>example-age-in-descending-order - php mysql examples | w3resource</title>
<meta name="description" content="example-age-in-descending-order - 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. The list is sorted against the name of the publisher in descending order:</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 ORDER BY Age DESC') 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>

View the example in browser

Previous: Date Calculation
Next: Date with not null



New Content: Composer: Dependency manager for PHP, R Programming


bedava porno