开发者

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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜