How do I get the creation date of a MySQL table?

Question

I was wondering if it is possible to get the date that a specific table in a database was created. My employer would like to add this feature and I have been unable to find a solution with Google.

I figured SO is the next best place to look.

Solution 1

You would query the information_schema for the create_time of the table.

For instance:

SELECT create_time FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'your_schema'
  AND table_name = 'your_table'

Reference: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html

Solution 2

I know that this is an old question but I wanted to suggest an alternative approach that doesn't involve having to directly query the information_schema tables for anyone who looks up this question later for reference.

You can obtain information about the tables using "show table status". It gives you plenty of information including the storage engine being used, the create time, the update time, and the number of rows.

You can filter the results based on the name of the table, as follows:

show table status where name = 'your-table-name'

Comments

[chiliNUT]

better answer than the duplicate question answer

[Bob Stein]

Apparently broken for InnoDB tables. create_time actually reports the most recent ALTER for all InnoDB tables.

[Bob Stein]

Does not work for InnoDB tables.

[Brian Lin]

Thanks for this, this worked great on phpMyAdmin.