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

User · Answer

A simple way can be this one  this is a real example from my code and works perfectly   where  actions created at  gt   DATE SUB CURDATE    INTERVAL 1 WEEK

User · Answer

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

User · Answer

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  lt    date       now this will give the date for a week ago

User · Answer

SELECT id  FROM tb1 WHERE  YEARWEEK  date    YEARWEEK  current date -interval 1 week

User · Answer

SELECT id FROM tbl WHERE date  gt   curdate   - INTERVAL DAYOFWEEK curdate    6 DAY AND date  lt  curdate   - INTERVAL DAYOFWEEK curdate   -1 DAY

User · Answer

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

User · Answer

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

User · Answer

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   gt   CURDATE   - INTERVAL 7 DAY

User · Answer

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

User · Answer

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

User · Answer

select id from tbname where date between date sub now   INTERVAL 1 WEEK  and now

User · Answer

Probably the most simple way would be   SELECT id FROM table WHERE date  gt   current date - 7   For 8 days  i e  Monday - Monday

User · Answer

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

User · Answer

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    gt  DATEADD  DAY  -7  CAST  GETDATE   AS DATE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

i Use this for the week start from SUNDAY   SELECT id FROM tbl WHERE date  gt   curdate   - INTERVAL DAYOFWEEK curdate    5 DAY   AND date  lt  curdate   - INTERVAL DAYOFWEEK curdate   -2 DAY

User · Answer

You can also use it esay way  SELECT   FROM   inventory WHERE  YEARWEEK  modify   1    YEARWEEK CURDATE    1

User · Answer

It can be in a single line    SELECT   FROM table WHERE Date BETWEEN  NOW   - INTERVAL 7 DAY  AND NOW

User · Answer

WHERE yourDateColumn  gt  DATEADD DAY  -7  GETDATE

[mysql] MySQL Query to select data from last week?

Examples related to mysql

Examples related to sql

Examples related to database

Examples related to datetime