

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySQL Query to find tables modified in the last hour?
You can achieve this with the help of INFORMATION_SCHEMA.TABLES. Use the date_sub() with interval. The syntax is as follows −
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < ‘UPDATE_TIME’;
Now you can check the above syntax. Here is the query to find the tables modified in the last hour −
mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES` -> WHERE -> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;
Output
+---------------------+ | TABLE_NAME | +---------------------+ | innodb_table_stats | | innodb_index_stats | | employeeinformation | +---------------------+ 3 rows in set (0.37 sec)
The above query selects only table name. If you want information like table schema, table type etc, you need to use the following query. The query displays the table modified in the last hour −
mysql> SELECT * -> FROM `INFORMATION_SCHEMA`.`TABLES` -> WHERE -> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;
The following is the output displaying the table information modified in the last hour −
+---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+ | def | mysql | innodb_table_stats | BASE TABLE | InnoDB | 10 | Dynamic | 449 | 145 | 65536 | 0 | 0 | 4194304 | NULL | 2018-09-22 20:37:26 | 2018-12-24 15:13:41 | NULL | utf8_bin | NULL | row_format=DYNAMIC stats_persistent=0 | | | def | mysql | innodb_index_stats | BASE TABLE | InnoDB | 10 | Dynamic | 1413 | 243 | 344064 | 0 | 0 | 4194304 | NULL | 2018-09-22 20:37:26 | 2018-12-24 15:13:41 | NULL | utf8_bin | NULL | row_format=DYNAMIC stats_persistent=0 | | | def | test | employeeinformation | BASE TABLE | InnoDB | 10 | Dynamic | 6 | 2730 | 16384 | 0 | 0 | 0 | NULL | 2018-12-24 15:12:04 | 2018-12-24 15:14:00 | NULL | utf8mb4_0900_ai_ci | NULL | | | +---------------+--------------+---------------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+---------------------------------------+---------------+ 3 rows in set (43.72 sec)
- Related Questions & Answers
- How to find tables modified in the last hour in Android sqlite?
- MySQL query to find the number of rows in the last query
- Fetching rows added in last hour with MySQL?
- A single MySQL query to find the highest and lowest among two tables?
- MySQL query to count rows in multiple tables
- How to get file last modified time in Java?
- MongoDB query to find last object in collection?
- What is the MySQL query to display the number of tables in a database?
- Find the greatest value among four tables in MySQL?
- MySQL query to get the last created table name (most recent)?
- MySQL query to search within the last 5 characters in a column?
- MySQL query with two boolean conditions to extract date based on hour?
- MySQL query to group data in the form of user login time per hour and get the records of the users logged in the recent hour?
- How to show the date and time the document was last modified with JavaScript?
- MySQL query to extract last word from a field?
Advertisements