MYSQL > Using yearweek() how to select past week

mysql free download
mysql tutorial
mysql workbench
mysql online
oracle mysql download
mysql w3schools
how to install mysql
mysql commands

How can I select all records from the last week ( starting from Monday last week) ?

I am trying to use

yearweek() in my sql query but i was only able to select this week records

Here is a way to do it. First we need to get the last week Monday from current date, so we can use the following technique

Below will always give the Monday of the current week

mysql> select date_sub(curdate(), interval weekday(curdate()) day) as d ;
+------------+
| d          |
+------------+
| 2016-09-19 |
+------------+

Now to get the Monday of the last week we can adjust the above as

mysql> select date_sub(date_sub(curdate(), interval weekday(curdate()) day), interval 7 day)  as d ;
+------------+
| d          |
+------------+
| 2016-09-12 |
+------------+

And finally to retrieve the data we can do as

select * from your_table
where 
date_column >= date_sub(date_sub(curdate(), interval weekday(curdate()) day), interval 7 day)

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_weekday

Best is to handle the date calculation i.e. last week Monday on application level and then pass the value to the query.

MySQL, MySQL Cluster enables users to meet the database challenges of next generation web, cloud, and communications services with uncompromising scalability,  One database for every workload & infrastructure. MemSQL is the cloud-native, operational database built for speed and scale.

The YEARWEEK operator takes an mode option that lets you start the week on Monday. Then just query for the YEARWEEK of NOW() minus 1

SELECT * FROM table
WHERE YEARWEEK(timestamp,1)=YEARWEEK(NOW(),1)-1

MySQL, The data in a MySQL database are stored in tables. A table is a collection of related data, and it consists of columns and rows. Databases are useful for storing  Select Sql Courses Based On Your Individual Skill Level, Budget, And Schedule.

See DAYOFWEEK()

Not seeing you table structe, start with this and adjust to your needs

SELECT * FROM table
WHERE date >= curdate() - INTERVAL DAYOFWEEK( curdate() )+ 7 DAY

PHP: MySQL Database, MySQL is a database management system that allows you to manage relational databases. It is open source software backed by Oracle. It means you can use  MySQL for OEM/ISV. Over 2000 ISVs, OEMs, and VARs rely on MySQL as their products' embedded database to make their applications, hardware and appliances more competitive, bring them to market faster, and lower their cost of goods sold.

What Is MySQL? & Why It Is the World's Most Popular Open Source , Oracle MySQL Database Service is a fully managed database service that enables organizations to deploy cloud native applications using the world's most​  MySQL is the world’s most popular open source database. With its proven performance, reliability, and ease-of-use, MySQL has become the leading database choice for web-based applications, used by high profile web properties including Facebook, Twitter, YouTube, and all five of the top five websites*. Additionally, it is an extremely popular

MySQL Database Service, MySQL is the most popular Open Source Relational SQL Database Management System. MySQL is one of the best RDBMS being used for developing various  MySQL is an open source relational database management system (RDBMS) that uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database.

MySQL Tutorial, mysql_affected_rows — Get number of affected rows in previous MySQL operation; mysql_client_encoding — Returns the name of the character set  MySQL (/ ˌ m aɪ ˌ ɛ s ˌ k juː ˈ ɛ l / "My S-Q-L") is an open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter, and "SQL", the abbreviation for Structured Query Language.

Comments
  • Sorry I was lost in the example can you make it more clean. can you give me on query ?
  • I use the last example and I got the total recoreds form last week and this week .. I only need to select from the last week
  • Please provide some sample data and expected result.. and I only need to select from the last week is it from last week Monday till Saturday or Monday till Sunday ?
  • Monday till Sunday. from 12-9 till 18-9 I have 200 record from 19-9 till 21-9(today) I have 100 record when I use your query I get 300 record, I should get 200 instead
  • Just change the where condition as where date_column between date_sub(date_sub(curdate(), interval weekday(curdate()) day), interval 7 day) and date_add( date_sub(date_sub(curdate(), interval weekday(curdate()) day), interval 7 day),interval 6 day)
  • thanks but this gives me records from 10 September until today , I need to select only between 12-18 September