mysql queries the data of the current day 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

Nearly 7 days

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

Nearly 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' )

Last January

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

Query the data of this quarter

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

Query last quarter data

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

Query data of this year

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

Query the data of the previous year

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

Query data for the current week

SELECT * FROM  Table name  WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  Time field name ) <= 1

0

Query last week's data

SELECT * FROM  Table name  WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  Time field name ) <= 1

1

Query last month's data

SELECT * FROM  Table name  WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  Time field name ) <= 1

2

Query the data of the current month

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

Query data 6 months away from the current one

SELECT * FROM  Table name  WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  Time field name ) <= 1

4

PS: Let's see how mysql queries the information of the day.

Originally is not very familiar with SQL query statement, what is used again to look for, but the network provides us with a lot of support. Today, I used a sentence again. I can't think of how to solve it. I looked at it on the Internet and felt that there was one. How is it so simple? There are so many things to accumulate.

Write down my simple question today! Is an accumulation:

mysql queries all information for the day:

SELECT * FROM  Table name  WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  Time field name ) <= 1

5

This is a bit cumbersome and simple to write:

SELECT * FROM  Table name  WHERE TO_DAYS( NOW( ) ) - TO_DAYS(  Time field name ) <= 1

6

The date () function takes the date part, throws away the time part, and compares it with the current date