基于mysql时间戳的截止日期后的PHP和mysql查询

Question

I've mysql DB, which stores timestamp (date + hour).

I'd like to use this data to create cutoff points, like week ago, month ago etc...

$result = $mysqli->query("SELECT 
u.user_id,
  ( SELECT sum(r.result_value)
    FROM logos_results r
    WHERE r.user_id = u.user_id
    AND r.result_date <= '$week' ) AS rsum,
u.user_name,
u.user_pic,
u.user_logos_level
FROM users u
ORDER BY rsum DESC
LIMIT 4");

My question is, how do I create week in php? Should I remove timestamp and use something without hours? Will this exact time decrease speed of query considerably? AND r.result_date <= '$week' this is the important part, is syntax correct?

Answer 1

Is there a reason why you won't use mysql ability to convert weeks to date or to select intervals by week?

To me it seems better than implementing your week to date logic in php.

Check this question.

SELECT sum(r.result_value)
    FROM logos_results r
    WHERE r.user_id = u.user_id
    AND r.result_date < NOW() - INTERVAL $num_of_weeks WEEK