MySQL Query to select data from last week?

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.

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

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

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.

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

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

PLEASE people... 'Last week' like the OP asked and where I was looking for (but found none of answers satisfying) is THE LAST WEEK.

If today is Tuesday, then LAST WEEK is Monday A WEEK AGO to Sunday A WEEK AGO.

So:

WHERE
    WEEK(yourdate) = WEEK(NOW()) - 1

Or for ISO weeks:

WHERE
    WEEK(yourdate, 3) = WEEK(NOW(), 3) - 1

Probably the most simple way would be:

SELECT id
FROM table
WHERE date >= current_date - 7

For 8 days (i.e. Monday - Monday)

Here is a way to get last week, month, and year records in MySQL.

Last Week

SELECT UserName, InsertTime 
FROM tblaccounts
WHERE WEEK(InsertTime) = WEEK(NOW()) - 1;

Last Month

SELECT UserName, InsertTime 
FROM tblaccounts
WHERE MONTH(InsertTime) = MONTH(NOW()) - 1;

Last YEAR

SELECT UserName, InsertTime 
FROM tblaccounts
WHERE YEAR(InsertTime) = YEAR(NOW()) - 1;

If you're looking to retrieve records within the last 7 days, you can use the snippet below:

SELECT date FROM table_name WHERE DATE(date) >= CURDATE() - INTERVAL 7 DAY;

You'll need to calc which day relative to today is Sunday in your middleware (php, python, etc.)*

Then,

select id
from table
where date >= "$sunday-date" + interval 7 DAY

• may be a way to get sunday's date relative to today in MySQL as well; that would be arguably the cleaner solution if not too expensive to perform

It can be in a single line:

SELECT * FROM table WHERE Date BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()

A simple way can be this one, this is a real example from my code and works perfectly:

where("actions.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)")

The above query will not work. After the where clause, if we can not CAST the column value, then it will not work. You should cast the column value.

e.g.:

SELECT.....
WHERE CAST( yourDateColumn AS DATE ) > DATEADD( DAY, -7, CAST( GETDATE() AS DATE )

For more example Like last month, last year, last 15 days, last 3 months

Fetch Last WEEK Record

Using the below MySQL query for fetching the last week records from the mysql database table.

SELECT name, created_at 
FROM employees
WHERE
YEARWEEK(`created_at`, 1) = YEARWEEK( CURDATE() - INTERVAL 1 WEEK, 1)

SELECT id  FROM tb1
WHERE 
YEARWEEK (date) = YEARWEEK( current_date -interval 1 week ) 
 

I often do a quick "last week" check as well and the following tends to work well for me and includes today.

DECLARE @StartDate DATETIME 
DECLARE @EndDate DATETIME 

SET @StartDate = Getdate() - 7 /* Seven Days Earlier */
SET @EndDate = Getdate() /* Now */

SELECT id 
FROM   mytable 
WHERE  date BETWEEN @StartDate AND @Enddate 

If you want this to NOT include today just subtract an extra day from the @EndDate. If I select these two variables today get

> @StartDate 2015-11-16 16:34:05.347 /* Last Monday */

>@EndDate 2015-11-23 16:34:05.347 /* This Monday */

If I wanted Sunday to Sunday I would have the following.

SET @StartDate = Getdate() - 8 /* Eight Days Earlier */
SET @EndDate = Getdate() - 1  /* Yesterday */

>@StartDate 2015-11-15 16:34:05.347 /* Previous Sunday */

>@EndDate 2015-11-22 16:34:05.347 /* Last Sunday */

WHERE yourDateColumn > DATEADD(DAY, -7, GETDATE()) ;

You can also use it esay way

SELECT *
FROM   inventory
WHERE  YEARWEEK(`modify`, 1) = YEARWEEK(CURDATE(), 1)

i Use this for the week start from SUNDAY:

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

try this

WHERE trunc(DATE) <= (trunc (sysdate) -5)  AND trunc(DATE) >= (trunc (sysdate) -12)

-5 is 5 days back from system date ,, -12 is 12 days back from system date for this example wednesday / or sednesday to wednesday cant recall.

Try this:

Declare @Daytype varchar(15),
		@StartDate datetime,
		@EndDate datetime
set @Daytype = datename(dw, getdate())

if @Daytype= 'Monday' 
	begin
		set @StartDate = getdate()-7 
		set @EndDate = getdate()-1

	end


else if @Daytype = 'Tuesday'

	begin
		set @StartDate = getdate()-8 
		set @EndDate = getdate()-2

	end
Else if @Daytype = 'Wednesday'
	begin
		set @StartDate = getdate()-9
		set @EndDate = getdate()-3
	end
Else if @Daytype = 'Thursday'
	begin
		set @StartDate = getdate()-10 
		set @EndDate = getdate()-4
	end

Else if @Daytype = 'Friday'

	begin
		set @StartDate = getdate()-11
		set @EndDate = getdate()-5

	end
 
Else if @Daytype = 'Saturday'

	begin
		set @StartDate = getdate()-12
		set @EndDate = getdate()-6

	end
 
Else if @Daytype = 'Sunday'

	begin
		set @StartDate = getdate()-13
		set @EndDate = getdate()-7

	end

 select @StartDate,@EndDate

You can try this one. it worked for me :

where date(createdtime) <= date(curdate())-7

In the the above code createdtime is database field name, as individuals this name could vary.

If you already know the dates then you can simply use between, like this:

SELECT id    
FROM `Mytable`    
where MyDate BETWEEN "2011-05-15" AND "2011-05-21"