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)
Published on 15-Apr-2019 13:47:39
