从MySQL获取最新记录（7天）

Question

I am trying to grab the newest records from my table. I want all of the records that happened in the past 7 days. Here is what I have so far to start with.

$query = "SELECT * FROM mlg_logattempts AS a WHERE a.ts = (SELECT MAX(ts) FROM mlg_logattempts AS b WHERE b.ts > NOW() - INTERVAL 5 MINUTE AND b.name = a.name)";

I have used intervals in the past but an unsure how to make this work now. Can someone show me the proper way to request the past 7 days records? I do have a timestamp field.

UPDATE

Unfortunately I realized the command I shared with you. I do not have any of the above fields. The only date field I have is "date". no a or ts.

Answer 1

You could use mysql date_diff() for dates http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

but since you use timestamps, the interval is a good solution:

b.ts > unix_timestamp(CURDATE()-INTERVAL 7 DAY)