MySQL Query to select data from last week?

mysql sql database datetime

255,575

Solution 1

SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

Solution 2

select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();

Solution 3

SELECT id FROM table1
WHERE YEARWEEK(date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)

I use the YEARWEEK function specifically to go back to the prior whole calendar week (as opposed to 7 days before today). YEARWEEK also allows a second argument that will set the start of the week or determine how the first/last week of the year are handled. YEARWEEK lets you to keep the number of weeks to go back/forward in a single variable, and will not include the same week number from prior/future years, and it's far shorter than most of the other answers on here.

Solution 4

Simplified form:

Last week data:

SELECT id FROM tbl


WHERE 
WEEK (date) = WEEK( current_date ) - 1 AND YEAR( date) = YEAR( current_date );

2 weeks ago data:

SELECT id FROM tbl


WHERE 
WEEK (date) = WEEK( current_date ) - 2 AND YEAR( date) = YEAR( current_date );

SQL Fiddle

http://sqlfiddle.com/#!8/6fa6e/2

Solution 5

You can make your calculation in php and then add it to your query:

$date = date('Y-m-d H:i:s',time()-(7*86400)); // 7 days ago

$sql = "SELECT * FROM table WHERE date <='$date' ";

now this will give the date for a week ago

View more solutions

255,575

Bojan Muvrin

please delete me

Updated on July 08, 2022

Comments

Bojan Muvrin almost 2 years
Hi I have a table with a date field and some other information. I want to select all entries from the past week, (week start from Sunday).

table values:
```
id  date
2   2011-05-14 09:17:25
5   2011-05-16 09:17:25
6   2011-05-17 09:17:25
8   2011-05-20 09:17:25
15  2011-05-22 09:17:25
```
I want to select all ids from last week, expected output is 5, 6, 8. (id 2 not in last week, and id 15 is in current week.)

How to write and SQL Query for the same.
- Brendan Long almost 13 years
  
  So just to clarify, you want data since sunday, not necessarily 7 days of data? So if today is Monday you'll get 2 days of data (Sunday and Monday)?
- Bojan Muvrin almost 13 years
  
  Sunday to Saturday. Not last 7 days
- Brendan Long almost 13 years
  
  I don't feel like actually converting this to SQL right now, but you can find an algorithm to determine the day of the week on Wikipedia, and then use that to decide how far back to look: en.wikipedia.org/wiki/…
- Jürgen Thelen almost 13 years
  
  @Brendan Long: he could use SELECT id FROM tbl WHERE WEEK(date, 0) = WEEK(NOW(), 0) - 1 for weeks, but I guess his target is not real calendar weeks. At least he didn't mention s/t like ISO-8601, or whether he wants turn of the year to be taken into consideration.
- Connor Leech almost 5 years
  
  For me answer is WHERE table.column >= DATE(NOW()) - INTERVAL 7 DAY
Christian Goetze over 10 years

What happens in the first week of the year?
Anam over 10 years

The question was to select data from last week? If first week of the year, and you want to select last week data, the query will return last week of the previous year.
Christian Goetze over 10 years

I'm just confused by the YEAR(date) = YEAR(current_date) then. How can that select the previous year?
Hawkins Entrekin over 9 years

This does not solve OP question if you ran this on '2011-05-22' you would get id 15...
Josh Burgess over 9 years

Please explain why your code answers the asker's question.
Xman Classical over 8 years

Hi, How to get last week with week defined start from Monday to Sunday
Xman Classical over 8 years

@Graph Yes, The SQL script above will be like that: SELECT id FROM tbl WHERE DATE > DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())+6 DAY) AND DATE <= DATE_SUB(DATE(NOW()), INTERVAL DAYOFWEEK(NOW())-1 DAY)
kazuar over 7 years

it returns last 7 days instead of last week
Jad Chahine about 7 years

This is the best answer.
squarewav about 6 years

Near as I can tell, this is the only answer that actually answers the OPs question correctly. The question was how to get records from the previous week. Many of the answers here are actually incorrect.
stamster over 5 years

If you want to specify start day of the week as well, supply 2nd argument to YEARWEEK(): WHERE YEARWEEK(dateColumnFromTbl, 1) = YEARWEEK(CURDATE() - INTERVAL 1 WEEK, 1)
gumuruh over 3 years

if i want to include the today's date as well... inside that last week...how was that?
danny about 3 years

can we use all three in one query? in sub queries?
sidverma almost 3 years

Share code snippet from any mysql editor. This way without formatting and code highlight it's not clear.
astentx almost 3 years

It selects previous dates, so does exactly opposite than requested. Also date(curdate()) does nothing meaningful compared to simple curdate().
John S over 2 years

Just be careful with this if you have more than 1 year of data as it will show you all of the same week for those previous years as well.
MS Berends over 2 years

Absolutely true, but that goes for pretty much all answers here. It would indeed require a GROUP BY or something similar.
azurecorn about 2 years

Not quite. For an example, speaking of month section, the code above would calculate a MONTH = 0, if "InsertTime" is in January. But, something like "WHERE MONTH(InsertTime) = MONTH(NOW() - INTERVAL 1 MONTH)" should work properly.