Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 30-Jul-2019
136 Views
Advertisements