MySQL query to display databases sorted by creation date?

MySQLMySQLi Database

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)
raja
Published on 15-Apr-2019 17:17:39
Advertisements