MySQL query to display databases sorted by creation date?

You can display databases sorted by creation date with ORDER BY clause. Following is the query to display all databases −

mysql> show databases;

This will produce the following output −

+---------------------------+
| Database                  |
+---------------------------+
| bothinnodbandmyisam       |
| business                  |
| commandline               |
| customer_tracker_database |
| customertracker           |
| database1                 |
| databasesample            |
| demo                      |
| education                 |
| hb_student_tracker        |
| hello                     |
| information_schema        |
| javadatabase2             |
| javasampledatabase        |
| mybusiness                |
| mydatabase                |
| mysql                     |
| onetomanyrelationship     |
| performance_schema        |
| rdb                       |
| sample                    |
| sampledatabase            |
| schemasample              |
| sys                       |
| test                      |
| test3                     |
| tracker                   |
| universitydatabase        |
| web                       |
| webtracker                |
+---------------------------+
30 rows in set (0.00 sec)

Following is the query to show databases sorted by creation date −

mysql> SELECT
   -> TABLE_SCHEMA AS ALL_DATABASE_NAME,
   -> MAX(create_time) AS creationTime,
   -> MAX(update_time) updatingTime
   -> FROM INFORMATION_SCHEMA.TABLES
   -> GROUP BY ALL_DATABASE_NAME
   -> ORDER BY creationTime DESC;

This will produce the following output −

+---------------------+---------------------+---------------------+
| ALL_DATABASE_NAME   | creationTime        | updatingTime        |
+---------------------+---------------------+---------------------+
| test                | 2019-04-03 11:37:58 | 2019-04-03 11:38:55 |
| hb_student_tracker  | 2019-03-19 03:54:32 | NULL                |
| sample              | 2019-03-15 00:04:29 | 2019-03-08 16:06:09 |
| test3               | 2019-03-12 20:29:12 | NULL                |
| mysql               | 2019-02-26 07:10:49 | 2019-04-03 11:38:56 |
| demo                | 2019-02-19 03:27:40 | NULL                |
| tracker             | 2019-02-14 19:49:55 | NULL                |
| bothinnodbandmyisam | 2019-02-06 14:32:26 | 2019-02-05 18:11:14 |
| commandline         | 2019-01-30 21:21:56 | NULL                |
| rdb                 | 2019-01-03 19:37:43 | NULL                |
| business            | 2019-01-02 17:32:17 | 2018-12-10 17:53:02 |
| education           | 2018-10-06 15:07:29 | NULL                |
| information_schema  | 2018-09-23 02:09:14 | NULL                |
| sys                 | 2018-09-23 02:09:03 | NULL                |
| performance_schema  | 2018-09-23 02:08:01 | NULL                |
+---------------------+---------------------+---------------------+
15 rows in set (0.05 sec)

Smita Kapse

Published on 15-Apr-2019 13:47:39

• Related Questions & Answers
• How do you get a directory listing sorted by creation date in Python?
• MySQL query to group results by date and display the count of duplicate values?
• Filter query by current date in MySQL
• MySQL query to display records ordered by numeric difference?
• How to get the creation date of a MySQL table?
• Increment date/time value by second with MySQL query?
• Display databases in MongoDB
• MySQL query to display records ordered by DESC while skipping some?
• Query about android databases
• MySQL query to order and display difference between dates from the current date
• Select query to display duplicate values with max date
• How do I get the creation date of a MySQL table?
• MySQL query to select date from timestamp?
• MySQL query to insert row with date?
• MySQL query to select date >= current date - 3 weeks?