Fetching rows with updated at timestamp older than 1 day in mysql

Question

I have a table with schema as follows:

mysql> desc a;
  +---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| created_at    | int(11)          | YES  |     | NULL    |                |
| updated_at    | int(11)          | YES  |     | NULL    |                |

In updated_at I capture timestamp.

Now I would like to fetch all rows with updated_at < NOW() - INTERVAL 1 DAY.

To do this, I have written following query taking idea from Fetching rows added last hour:

mysql> select count(*) from a where DATE(updated_at) < DATE_SUB(NOW(),INTERVAL 1 DAY);
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Though there are many rows with updated at with 2 years old timestamp, I am getting 0 here.

What sort of values are in `updated_at`? – Nick Apr 05 '20 at 06:29 — Nick, Apr 05 '20 at 06:29
@Nick timestamp values are in updated_at – Joy Apr 05 '20 at 06:30 — Joy, Apr 05 '20 at 06:30

score 3 · Accepted Answer · answered Apr 05 '20 at 06:32

You can't use DATE on an integer timestamp value; you must convert it to a DATE using FROM_UNIXTIME:

SELECT COUNT(*) 
FROM a
WHERE FROM_UNIXTIME(updated_at) < NOW() - INTERVAL 1 DAY

Note that you don't have to use DATE_SUB to subtract an interval, you can just write the arithmetic directly.

Fetching rows with updated at timestamp older than 1 day in mysql

1 Answers1