Detailed introduction to Mysql date query

Detailed introduction to Mysql date query

Query the current date

SELECT CURRENT_DATE();
SELECT CURDATE();

Query the current date and time

SELECT NOW();

Query today's data

SELECT * FROM `table name` WHERE TO_DAYS(NOW()) = TO_DAYS(`field`);
SELECT * FROM `table name` WHERE TO_DAYS(NOW()) - TO_DAYS(`field`) = 0;

Query yesterday's data

SELECT * FROM ``table name`` WHERE TO_DAYS(`field`) = TO_DAYS(NOW()) -1;
SELECT * FROM ``table name`` WHERE TO_DAYS(`field`) - TO_DAYS(NOW()) = -1;
SELECT * FROM ``table name`` WHERE TO_DAYS(NOW()) = TO_DAYS(`field`) +1;
SELECT * FROM ``table name`` WHERE TO_DAYS(NOW()) - TO_DAYS(`field`) = 1;

Query the data for the last seven days

SELECT * FROM `table name` WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= DATE(`field`);

Query the data for the last thirty days

SELECT * FROM table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= DATE(`field`);

Query this week's data

SELECT * FROM `table name` WHERE YEARWEEK(date_format(`field`,'%Y-%m-%d')) = YEARWEEK(CURDATE());
SELECT * FROM `table name` WHERE YEARWEEK(date_format(`field`,'%Y-%m-%d')) - YEARWEEK(CURDATE()) = 0;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) = YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d'));
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) - YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d')) = 0;

Query last week's data

SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) = YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d')) - 1;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) - YEARWEEK(DATE_FORMAT(CURDATE(),'%Y-%m-%d')) = -1;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) = YEARWEEK(CURDATE()) - 1;
SELECT * FROM `table name` WHERE YEARWEEK(DATE_FORMAT(`field`,'%Y-%m-%d')) - YEARWEEK(CURDATE()) = -1;

Query data for the current month

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y-%m') = DATE_FORMAT(CURDATE(),'%Y-%m');
SELECT * FROM `table name` WHERE MONTH(`field`) - MONTH(NOW()) = 0;
SELECT * FROM `table name` WHERE MONTH(`field`) = MONTH(NOW());

Query last month's data

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y-%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%Y-%m');

Query data for this quarter

SELECT * FROM `table name` WHERE QUARTER(`field`) = QUARTER(NOW()) AND YEAR(`field`) = YEAR(NOW());

Query the data of the previous quarter

SELECT * FROM `table name` WHERE QUARTER(`field`) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) AND YEAR(`field`) = YEAR(NOW());

Query data for the first half of the year (six months)

SELECT * FROM `table name` WHERE `field` BETWEEN DATE_SUB(NOW(),INTERVAL 6 MONTH) AND NOW();

Query this year's data

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y') = DATE_FORMAT(NOW(),'%Y');
SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y') - DATE_FORMAT(NOW(),'%Y') = 0;
SELECT * FROM `table name` WHERE YEAR(NOW()) = YEAR(`field`);
SELECT * FROM `table name` WHERE YEAR(NOW()) - YEAR(`field`) =0;

Query last year's data

SELECT * FROM `table name` WHERE DATE_FORMAT(`field`,'%Y') - DATE_FORMAT(NOW(),'%Y') = -1;
SELECT * FROM `table name` WHERE YEAR(NOW()) = YEAR(`field`) + 1;
SELECT * FROM `table name` WHERE YEAR(NOW()) - YEAR(`field`) = 1;

This is the end of this article about the detailed introduction of MySQL date query. For more relevant MySQL date query content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Detailed explanation of date formatting in MySQL
  • Detailed explanation of mysql get current date function and time formatting parameters
  • How to query date and time in mysql
  • How to query records between two dates in MySQL
  • Detailed explanation of MySql date query statement
  • Mysql date formatting and complex date range query

<<:  How to dynamically add modules to Nginx

>>:  Vue el-date-picker dynamic limit time range case detailed explanation

Recommend

HTML table tag tutorial (11): horizontal alignment attribute ALIGN

In the horizontal direction, you can set the alig...

One sql statement completes MySQL deduplication and keeps one

A few days ago, when I was working on a requireme...

Summary of solutions for MySQL not supporting group by

I downloaded and installed the latest version of ...

How to implement hot deployment and hot start in Eclipse/tomcat

1. Hot deployment: It means redeploying the entir...

Some experience sharing on enabling HTTPS

As the domestic network environment continues to ...

JavaScript implements simple calculator function

This article example shares the specific code of ...

jQuery implements dynamic tag event

This article shares the specific code of jQuery t...

Use dockercompose to build springboot-mysql-nginx application

In the previous article, we used Docker to build ...

Install three or more tomcats under Linux system (detailed steps)

If you want to install multiple tomcats, you must...

Example verification MySQL | update field with the same value will record binlog

1. Introduction A few days ago, a development col...

A brief discussion on the use of GROUP BY and HAVING in SQL statements

Before introducing the GROUP BY and HAVING clause...

Linux loading vmlinux debugging

Loading kernel symbols using gdb arm-eabi-gdb out...

VS2019 connects to mysql8.0 database tutorial with pictures and text

1. First, prepare VS2019 and MySQL database. Both...

Use pictures to realize personalized underline of hyperlinks

Don't be surprised if you see some kind of und...