MySQL: Table Creation Date

I recently received an email from one of my clients regarding an error that she was getting on her website. It was a missing table error. I had made some changes over a month ago which required me to add a ‘states’ table. It was interesting because I knew that I had uploaded the table to her server and tested the new functionality.

I then remembered that the host provider had recently upgraded their servers (I use the same host provider). I ran into problems after the upgrade. Any changes that I had made on my site during the time of the upgrade were not reflected on the new server. I had draft posts that were no longer stored in the MySQL database.

To make a long story short (too late), I needed to test whether I created the ‘states’ table during the same time frame as the upgrade. Because I always make and test changes on my computer prior to uploading to a server, I decided to query the table schema on my computer to find out when I created the states table. I knew that MySQL stores database, table, table columns (etc) in the information_schema database. I just wasn’t sure which table the information would be stored in. After a quick search, I found a TABLES table, but I did not know which field to query to find the creation date:

SELECT column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'TABLES'

This gave me a list of field names in the TABLES table. I found the field CREATE_TIME.

SELECT create_time
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'arrowcollegiatetour'
AND table_name = 'states'

I found that I created the ‘states’ table during the same time frame as the server upgrades, so I sent my client a message telling her to contact the host provider and have them recreate her site from their daily backups. I just love MySQL.