MYSQL get data within an certain time frame
hey everyone, having problem getting this MYSQL query correct
SELECT *
FROM tbl_problem
WHERE user_id = 1
AND problem_so开发者_如何转开发lved != -1
AND problem_timestamp BETWEEN '20110212' AND DATE('20110212', INTERVAL 14 DAY)
ORDER BY problem_id
the problem is 20110212 is dynamically generated by php, which is why i use the DATE/INTERVAL COMBO.
what i am trying to do is select entries within a 2 week time frame given that you know the start date of that two week cycle.
thanks in advance
Why not just get PHP to reformat it and then query for
SELECT *
FROM tbl_problem
WHERE user_id = 1
AND problem_solved != -1
problem_timestamp > '2011-01-24' AND problem_timestamp < DATE_ADD('2011-02-12', INTERVAL 14 DAY)
ORDER BY problem_id
To convert the date in PHP from your current format to MYSQL's format, use
$date = date_create_from_format('Ymd', '20110212');
$mysqlDate = date_format($date, 'Y-m-d'); //2011-01-24
Use Datediff.
SELECT *
FROM tbl_problem
WHERE user_id = 1
AND problem_solved != -1
AND DATEDIFF(problem_timestamp,'20110112') <= 14
ORDER BY problem_id
精彩评论