MySQL Query to select data from last week?

Question

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.

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 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.

Answer 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

Answer 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

Answer 6

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

Answer 7

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;

Answer 8

Probably the most simple way would be:

SELECT id
FROM table
WHERE date >= current_date - 7

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

Answer 9

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

Answer 10

It can be in a single line:

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

Answer 11

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)")

Answer 12

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 )

Answer 13

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)

Answer 14

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;

Answer 15

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

Answer 16

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 */

Answer 17

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

Answer 18

You can also use it esay way

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

Answer 19

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

Answer 20

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

Answer 21

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.

Answer 22

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"