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)