MySQL Query to select data from last week?

Question

Hi I have a table with a date field and other information. I want to select all entries for the last week (beginning of the week 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 identifiers from last week, the expected result is 5, 6, 8. (id 2 is not last week, but id 15 is this week.)

How to write and SQL query for the same.

Answer 1

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

Answer 2

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

Answer 3

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

I use the YEARWEEK function specifically to return to the previous whole calendar week (as opposed to 7 days until today). YEARWEEK also enables a second argument that sets the start of the week or determines how the first / last week of the year is handled. YEARWEEK allows you to hold the number of weeks to go back / forward in one variable and will not include the same week number from previous / future years, and this is much shorter than most of the other answers here.

Answer 4

Simplified form:

Data for the last week:

 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

Answer 5

You can do your calculation in php and then add it to your request:

 $date = date('Ymd H:i:s',time()-(7*86400)); // 7 days ago $sql = "SELECT * FROM table WHERE date <='$date' "; 

now it will give a date a week ago

Answer 6

Probably the easiest way:

 SELECT id FROM table WHERE date >= current_date - 7 

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

Answer 7

You will need to calculate which day compared 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 a Sunday date compared to today in MySQL; this would probably be a cleaner solution if it weren’t too expensive to implement

Answer 8

It can be in one line:

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

Answer 9

This can be a simple way, this is a real example from my code and works fine:

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

Answer 10

PLEASE people ... "Last week", as the OP asked, and where I searched (but did not find a single answer that satisfies), - LAST WEEK.

If today is Tuesday, then THE LAST WEEK IS THE LAST WEEKS OF THE CENTURY ON AN EVENING AGRICULTURE.

So:

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

Or for ISO weeks:

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

Answer 11

The above query will not work. After the where clause, if we cannot CAST the column value, this will not work. You must CAST the column value.

eg:.

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

Answer 12

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

Answer 13

I often do a quick check last week, 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 NOT to turn on today, just subtract the extra day from @EndDate. If I select these two variables now, we 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 * /

Answer 14

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

Answer 15

You can also use it esay way

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

Answer 16

i Use this to start the week with SUNDAY:

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

Answer 17

For more examples. Like last month, last year, last 15 days, last 3 months

Get the last week of recording

Using the MySQL query below to retrieve records from the last week from the mysql database table.

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

Answer 18

Try the following:

 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 

Answer 19

If you already know the dates, you can simply use between them, for example:

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