MYSQL database is used by many applications and in most of the cases users struggle to get the list of tables which are modified and updated recently using application GUI.
Most of the time users try to find out:
- How to get the last modified date of a table?
- When a mysql table was last updated?
- How to find out table updated during last one hour?
- How to get exact date and time of a table when it was last updated?
Below are the list of queries using which you can find out recently modified tables in MYSQL.
List of tables updated in last 5 Minutes:
SELECT TABLE_SCHEMA,TABLE_NAME,UPDATE_TIME
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
DATE_SUB(NOW(), INTERVAL 5 MINUTE) < `UPDATE_TIME`;
List of tables updated in last one Hour:
SELECT *
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE
DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;
Find updated time based on table name:
SELECT UPDATE_TIME
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'DBNAME'
AND TABLE_NAME = 'TABLE_NAME';
Based on column name :timestamp
SELECT MAX(updated_at) FROM table_name;