Retrieve records using epoch date in mysql-CodePudding

I have a table named as data_stats. Please check below respective schema:

CREATE TABLE `data_stats` (
  `uuid` varchar(255) PRIMARY KEY NOT NULL COMMENT 'Primary Key',
  `state` varchar(255) NOT NULL DEFAULT 'Active',
  `created_at` BIGINT(20) NOT NULL COMMENT 'Timestamp of when the record was created',
  `updated_at` BIGINT(20) NOT NULL COMMENT 'Last updated timestamp');

I am storing created_at and update_at timestamp in the epoch format.

Now I need to find all the records that are updated today (i.e update_at date == today's date)

I tried below query

SELECT * FROM `data_stats` where FROM_UNIXTIME(updated_at,"%Y-%m-%d") = CURRENT_DATE

But I am getting empty results. So how can fetch records that are updated today?

CodePudding user response：

Could be a mismatch in the types on the equals. To make sure, check to see if coercing both sides to DATE helps. Replace FROM_UNIXTIME(updated_at,"%Y-%m-%d") = CURRENT_DATE with:

DATE(FROM_UNIXTIME(updated_at)) = DATE(CURRENT_DATE)