MySQL's Yearweek function and querying this week's data
MySQL's Yearweek is a function that gets the year and week numbers, in the form of Yearweek (Date[,mode])
For example, 2010-3-14, Sunday
SELECT yearweek (' 2010-3-14 ') returns 11
SELECT yearweek (' 2010-3-14 ', 1) returns 10
The second parameter is mode, which means the following:
| Mode |
First day of week |
Range |
Week 1 is the first Week ... |
| 0 |
Sunday |
0-53 |
With a Sunday inch |
| 1 |
Monday |
0-53 |
than 3 days |
| 2 |
Sunday |
1-53 |
With a Sunday inch |
| 3 |
Monday |
1-53 |
than 3 days |
| 4 |
Sunday |
0-53 |
than 3 days |
| 5 |
Monday |
0-53 |
With a Monday inch |
| 6 |
Sunday |
1-53 |
than 3 days |
| 7 |
Monday |
1-53 |
With a Monday inch |
select Yearweek (Now (), 1)-Yearweek (' 2014-06-23 ', 1) from dual --queries for the given date and the current date for the same one-week period, Monday is the first day of the week. Query Current week's data select name,submittime from Enterprise WHERE yearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ()); queries last week's data select Name,submittime from Enterprise WHERE yearweek (Date_format (submittime, '%y-%m-%d ') = Yearweek (now ()) -1; query data for the current month select Name,submittime from enterprise where Date_format ( Submittime, '%y-%m ') =date_format (now (), '%y-%m ') Query the data from the current 6 month select Name,submittime from Enterprise where Submittime between Date_sub (now (), interval 6 months) and now (); 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 (F Rom_unixtime (pudate, '%y-%m-%d ')) = month (now ()), select * from [user] where pudate between last day of last month and first day of next month;
