CODEWITHSUNDEEP
mysqldatetimestamp
akhasis
akhasis

Reputation: 89

MySQL select rows that are exactly 7 days old FROM TIMESTAMP

first of all, I know that my question is very similar to that one:

MySQL select rows from exactly 7 days ago

the difference is that my dates are stored in the database as a timestamp.

I know that I can use FROM_UNIXTIME to get the date from the timestamp, the thing is, in another answer I read that was very resource consuming (because the timestamp field has to be converted to date in all the records before comparing).

DATE(from_unixtime(timestamp)) = CURRENT_DATE()

Is there any optimized way to do this?

Upvotes: 1

Views: 3052

Answers (1)

Bart Friederichs
Bart Friederichs

Reputation: 33511

Turn it around: calculate the unix timestamp of the target date first and use that.

WHERE timestamp = UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)

MySQL should calculate that value once and use it all the time (needs testing though). If it doesn't, use a variable or code.

Upvotes: 5

Related Questions