Home>Article>Database> How to query the month in mysql

How to query the month in mysql

藏色散人
藏色散人 Original
2021-11-26 14:04:41 15052browse

Mysql method of querying the month: 1. Query the current month through "select date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH), '%m')"; 2. Query through "INTERVAL 1 MONTH" months.

How to query the month in mysql

The operating environment of this article: Windows 7 system, mysql 5.0, Dell G3.

mysql How to query the month?

mysql query the month data.

//查看本月数据 SELECT * FROM content_publish WHERE date_format(publish_time, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH),'%Y %m') //查看上个月数据 SELECT * FROM content_publish WHERE date_format(publish_time, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y %m') //查询上上个月数据 SELECT * FROM content_publish WHERE date_format(publish_time, '%Y %m') = date_format(DATE_SUB(curdate(), INTERVAL 2 MONTH),'%Y %m') //查询当前月份 select date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH), '%m') //查询上个月月份 select date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%m') //查询上上个月月份 select date_format(DATE_SUB(curdate(), INTERVAL 0 MONTH), '%m')

Today

select * from 表名 where to_days(时间字段名) = to_days(now());

Yesterday

SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

Nearly 7 days

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

Nearly 30 days

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

This month

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

Previous month

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

Query this quarter’s data

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 current week’s data

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 上月最后一天 and 下月第一天

Query the data of the current month

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

Query the data 6 months from now

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

Recommended study: "mysql video tutorial

The above is the detailed content of How to query the month in mysql. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn