mysql query data for today, this week, this month, and last month

today

select * from table name where to_days(time field name) = to_days(now());

yesterday

SELECT * FROM table name WHERE TO_DAYS( NOW( ) ) - TO_DAYS( time field name) <= 1

Last 7 days

SELECT * FROM table name where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(time field name)

Last 30 days

SELECT * FROM table name where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(time field name)

this month

SELECT * FROM table name WHERE DATE_FORMAT(time field name, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

Previous month

SELECT * FROM table name WHERE PERIOD_DIFF( date_format( now(), '%Y%m' ) , date_format( time field name, '%Y%m' ) ) = 1

Query data for this quarter

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

Query last quarter's data

select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

Query this year's data

select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

Query last year's data

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

Query the data for the current week

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

Query last week's data

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

Query last month's data

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m'); 
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 
select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 
select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 
select * from user where pudate between last day of previous month and first day of next month

Query data for the current month

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

Query data from 6 months ago

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

PS: Let’s see how to query the current day’s information in MySQL.

It turned out that I was not very familiar with SQL query statements, and I had to look up everything I needed before I could use it. Fortunately, the Internet provided us with a lot of support. I used a statement again today, and I couldn't figure out how to solve it for a while. I looked it up online and it seemed like there was a solution. How could it be so simple? There are so many things that need to be accumulated.

Let me record this simple question of mine today! It can be regarded as an accumulation:

mysql query all information of the day:

select * from test where year(regdate)=year(now()) and month(regdate)=month(now()) and day(regdate)=day(now())

This is a bit complicated, but there is a simple way to write it:

select * from table where date(regdate) = curdate();

The date() function gets the date part, discards the time part, and then compares it with the current date.

You may also be interested in:

How to query duplicate data in mysql table
Some methods to optimize query speed when MySQL processes massive data
How to query json in the database in mysql5.6 and below
Node.js database operation: querying MySQL database (Part 2)
MySQL uses the select statement to query the data of the specified column (field) in the specified table
How to connect to MySQL database and query content in Java
PHP introductory tutorial: How to use MySQL to operate the database (connection, query, transaction rollback, etc.)
MySql database query results are output in a table PHP code example
How to query MySQL database in PHP and save the results to an array

<<: Detailed explanation of execution context and call stack in JavaScript

>>: Very practical Tomcat startup script implementation method