Mysql databases are queried by day, yesterday, the first seven days, the last thirty days, the quarter, and the year. mysql databases are queried by thirty days.

Source: Internet
Author: User

Mysql databases are queried by day, yesterday, the first seven days, the last thirty days, the quarter, and the year. mysql databases are queried by thirty days.

Today, I spent some time sorting out the code for MySQL to find data for the current day, yesterday, last week, last month, and so on.

1. query statements of today's data

Select * frim table name where to_days (time field name) = to_days (now ());
Select now (); // obtain the current time format: 23:21:33
Select curdate (); // obtain the current time format:
Select curtime (); // obtain the current time format: 23: 19: 43

2. Statement for querying yesterday's data

Select * from table name where to_days (now ()-to_days (time field name) = 1;

3. query seven-day statements

Select * from table name where date_sub (curdate (), interval 7 day) <= date (time field name ); or select * from table name where time field Name> = date_sub (curdate (), interval 7 day );

4. query statements for the last month

Select * from table name where date_sub (curdate (), interval 1 month) <= date (time field name ); or select * from table name where time field> = date_sub (curdate (), interval 1 month );

5. query data for the current quarter

Select * from table name where QUARTER (time field name) = QUARTER (now ());
QUARTER (date); // The date of the year returned. The value range is 1 to 4 quarters.

6. query the data of the last quarter

Select * from table name where QUARTER (time field name) = QUARTER (DATE_SUB (now (), interval 1 QUARTER ));

6. query data of the current year

Select * from table name where YEAR (time field name) = YEAR (NOW ());

7. query last year's data

Select * from table name where year (time field name) = year (date_sub (now (), interval 1 year ));

8. query data for the current week

SELECT name, time field name FROM table name where yearweek (date_format (submittime, '% Y-% m-% D') = YEARWEEK (now ());

9. query last week's data

SELECT name, time field name FROM table name where yearweek (date_format (submittime, '% Y-% m-% D') = YEARWEEK (now ()-1;

10. query the data of the current month

Select name, time field name from table name where date_format (submittime, '% Y-% m') = date_format (now (),' % Y-% m ')

11. query the data of the previous month

Select name, time field name from table name where date_format (time field name, '% Y-% m') = date_format (DATE_SUB (curdate (), INTERVAL 1 MONTH ), '% Y-% m ');
DATE_FORMAT (); // function is used to display date/time data in different formats.

12. MySQL Date and Time Functions

DAYOFWEEK (date)
The return date is the day of the week (1 = Sunday, 2 = Monday ,...... 7 = Saturday, ODBC Standard)
Mysql> select DAYOFWEEK ('2017-02-03 ');-> 3
WEEKDAY (date)
Returns the date of a week (0 = Monday, 1 = Tuesday ,...... 6 = Sunday)
Mysql> select WEEKDAY ('2017-10-04 22:23:00 ');-> 5
Mysql> select WEEKDAY ('2017-11-05 ');-> 2
DAYOFMONTH (date)
Returns the day (within the range of 1 to 31) from January 1, January)
Mysql> select DAYOFMONTH ('2017-02-03 ');-> 3
DAYOFYEAR (date)
Returns the day (within the range of 1 to 366) of a year)
Mysql> select DAYOFYEAR ('2017-02-03 ');-> 34
MONTH (date)
Returns the month value in date.
Mysql> select MONTH ('2014-02-03 ');-> 2

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.