How can I the date of creation and updation of tables in MySQL?

Get the create and update tables exact date using the create_time or update_time in MySQL.

At first, use the SHOW command. The syntax is as follows

SHOW TABLE STATUS;

We are considering our database ‘test3’, which is already having some tables

mysql> use test3;
Database changed

Now use the following query to display all the tables in the database test3

mysql> show tables;

The following is the output

+-------------------------+
| Tables_in_test3         |
+-------------------------+
| add6hour                |
| deletedemo              |
| differentdatetime       |
| fieldlessthan5chars     |
| lastrecordbeforelastone |
| mostrecentdatedemo      |
| nullcasedemo            |
| order                   |
| orderbydatethentimedemo |
| posts                   |
| productdemo             |
| radiansdemo             |
| siglequotesdemo         |
| studentinformation      |
| updatestringdemo        |
+-------------------------+
15 rows in set (0.00 sec)

The query is as follows to determine the dates. This shows the table status

mysql> show table status;

The following is the output displaying the entire status of the table. With that, we can also see the information we needed, for example, the creation and update date of the tables

+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name                    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length |Index_length  | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| add6hour                | InnoDB | 10       | Dynamic   | 0    | 0              | 16384       | 0               | 0            | 0         |1 | 2019-02-14 18:33:27 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| deletedemo              | InnoDB | 10       | Dynamic   | 6    | 2730           | 16384       | 0               | 0            | 0         |NULL | 2019-02-13 19:20:04 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| differentdatetime       | InnoDB | 10       | Dynamic   | 4    | 4096           | 16384       | 0               | 0            | 0         |5 | 2019-02-14 10:26:28 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| fieldlessthan5chars     | InnoDB | 10       | Dynamic   | 8    | 2048           | 16384       | 0               | 0            | 0       |8 | 2019-02-13 19:03:05 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| lastrecordbeforelastone | InnoDB | 10       | Dynamic   | 8    | 2048           | 16384       | 0               | 0            | 0       | 10 | 2019-02-14 17:31:59 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| mostrecentdatedemo      | InnoDB | 10       | Dynamic   | 6    | 2730           | 16384       | 0               | 0            |0         | 6 | 2019-02-14 17:15:53 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| nullcasedemo            | InnoDB | 10       | Dynamic   | 6    | 2730           | 16384       | 0               | 0            | 0        |NULL | 2019-02-14 17:55:39 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| order                   | InnoDB | 10       | Dynamic   | 8    | 2048           | 16384       | 0               | 0            | 0        |NULL | 2019-02-14 14:23:38 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| orderbydatethentimedemo | InnoDB | 10       | Dynamic   | 4    | 4096           | 16384       | 0               | 0            |0          | 5 | 2019-02-14 18:05:36 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| posts                   | InnoDB | 10       | Dynamic   | 0    | 0              | 16384       | 0               | 32768        | 0         |NULL | 2019-02-13 18:19:06 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| productdemo             | InnoDB | 10       | Dynamic   | 4    | 4096           | 16384       | 0               | 0            | 0         |NULL | 2019-02-13 20:06:36 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| radiansdemo             | InnoDB | 10       | Dynamic   | 2    | 8192           | 16384       | 0               | 0            | 0         |3 | 2019-02-14 14:33:24 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| siglequotesdemo         | InnoDB | 10       | Dynamic   | 0    | 0              | 16384       | 0               | 0            | 0         |NULL | 2019-02-14 10:41:23 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| studentinformation      | InnoDB | 10       | Dynamic   | 0    | 0              | 16384       | 0               | 0            | 0         |NULL | 2019-02-14 17:06:07 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
| updatestringdemo        | InnoDB | 10       | Dynamic   | 2    | 8192           | 16384       | 0               | 0            | 0         |3 | 2019-02-13 18:25:59 | NULL | NULL | utf8mb4_0900_ai_ci | NULL | | |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
15 rows in set (0.72 sec)

Arjun Thakur

Published on 19-Mar-2019 12:57:48

• Related Questions & Answers
• How do I get the creation date of a MySQL table?
• How to get the creation date of a MySQL table?
• How can I change the default sort order of MySQL tables?
• How can I see the description of a MySQL Temporary Tables?
• How Can I check the size of the tables in a particular MySQL database?
• How can I merge two MySQL tables?
• How can we see MySQL temporary tables in the list of tables?
• How can I get the output of multiple MySQL tables from a single query?
• While connecting to one MySQL database, how can I see the list of tables of other MySQL database?
• How can I use MySQL INTERVAL keyword while extracting the part of the date?
• How can I sum columns across multiple tables in MySQL?
• How can I check the tables of databases other than current database?
• How to get creation and modification date/time of a file using Python?
• How to set creation and modification date/time of a file using Python?
• How can I find the percentage of my users whose birth date is between 1980 and 1996 in MySQL?