MySQL YEARWEEK function and query data of this week _ MySQL

MySQL YEARWEEK function and query this week's data bitsCN.com

MySQL YEARWEEK function and query data of this week

MySQL YEARWEEK is a function used to obtain the year and week number. the Function form is YEARWEEK (date [, mode]).

For example, Sunday

Select yearweek ('2017-3-14 ') returns 11
Select yearweek ('2014-3-14 ', 1) returns 10

The second parameter is mode, which means:

Mode

First day of week

Range

Week 1 is the first week...

0

Sunday

0-53

With a Sunday in this year

1

Monday

0-53

With more than 3 days this year

2

Sunday

1-53

With a Sunday in this year

3

Monday

1-53

With more than 3 days this year

4

Sunday

0-53

With more than 3 days this year

5

Monday

0-53

With a Monday in this year

6

Sunday

1-53

With more than 3 days this year

7

Monday

1-53

With a Monday in this year

Query 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 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 from the current time

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

Query data of last month

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 the last day of the previous month and the first day of the next month;

BitsCN.com