I want to run a MYSQL query to get data for the previous week. The datatype for the date column is DATETIME. Could anyone suggest?
-
1Hmm, I think your question might suffer from posting/editing issues. Please clarify or read the FAQ.span– span2013-02-11 11:02:06 +00:00Commented Feb 11, 2013 at 11:02
-
Previous week? or last seven days?Strawberry– Strawberry2013-02-11 11:11:52 +00:00Commented Feb 11, 2013 at 11:11
-
stackoverflow.com/questions/12493995/get-data-from-last-week , And typecast your datetime to date stackoverflow.com/questions/4740612/… if it was what you were searching for, then my opinion is do some more google :)arjuncc– arjuncc2013-02-11 11:13:57 +00:00Commented Feb 11, 2013 at 11:13
-
For the last seven days...ashish– ashish2013-02-11 11:59:54 +00:00Commented Feb 11, 2013 at 11:59
Add a comment
|
5 Answers
SELECT *
FROM calendar
WHERE dt BETWEEN CURDATE()-INTERVAL 1 WEEK AND CURDATE();
Comments
Here is an another version:
SELECT * FROM table WHERE
YEARWEEK(`date`, 1) = YEARWEEK( CURDATE() - INTERVAL 1 WEEK, 1)
Comments
SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
Comments
Here is the solution I find most reliable for getting data between the previus monday to the current monday. (That is what most people mean when the say past week, but not all, and that reflect in mysql).
SELECT
*
FROM
table
WHERE
date BETWEEN
(CURDATE() - INTERVAL 1 DAY) + INTERVAL -1 WEEK - INTERVAL WEEKDAY((CURDATE() - INTERVAL 1 DAY)) DAY
and
(CURDATE() - INTERVAL 1 DAY) + INTERVAL 0 WEEK - INTERVAL WEEKDAY((CURDATE() - INTERVAL 1 DAY)) DAY
It's also easy to change it for another week intervall
Comments
Make variable for current datetime - 1 week and make this query:
SELECT * FROM table WHERE date > $datatime
