1

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?

Community
  • 1
  • 1
akhasis
  • 89
  • 1
  • 8

1 Answers1

5

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.

Bart Friederichs
  • 33,050
  • 15
  • 95
  • 195