MySQL Query today/this week/last week/this month/last month's data

MySQL queries a lot of ways, the following for you to introduce the MySQL query is to query this week, last week, this month, the last month's data, if you are interested in MySQL query, may wish to see.

Query the current data today
SELECT Name,submittime from Enterprise WHEREYearweek (Date_format (submittime, '%y-%m-%d ')) =Date_format(now (),'%y-%m-%d ');


querying data for the current week
SELECT Name,submittime from Enterprise WHEREYearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ());


query last week's data
SELECT Name,submittime from Enterprise WHEREYearweek (Date_format (submittime, '%y-%m-%d ')) = Yearweek (now ())-1;


querying data for the current month
Select Name,submittime from Enterprise wheredate_format (submittime, '%y-%m ') =date_format (now (), '%y-%m ')


Query data that is currently 6 months from now
Select Name,submittime from Enterprise wheresubmittime between Date_sub (now (), Interval 6 month) and now ();


querying data for the previous month
Select Name,submittime from Enterprise wheredate_format (submittime, '%y-%m ') =date_format (Date_sub (Curdate (), INTERVAL 1 MONTH), '%y-%m ')

SELECT * from ' user ' wheredate_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 last month
And the first day of next month

############################

By default, this Yearweek, WEEK (date) is every Sunday, as the beginning of the week.

Some started in Sunday, and some began in Monday. And some times in the work of the week is starting from Saturday, some data need weekly statistics, then that way is better? According to the following study, Yearweek () is considered to be suitable, and it returns 201311 in such a format. To take Saturday as the beginning of a new week, that is: Select Yearweek (Date_add (now (), INTERVAL 1 days)); Mysql> Select Yearweek (Date_add (now (), INTERVAL 1 day)) +-------------------------------------------+|                                    Yearweek (Date_add (now (), INTERVAL 1 day)) |+-------------------------------------------+| 201311 |+-------------------------------------------+1 row in Set mysql> select Yearweek (now ()); +----------------- +|          Yearweek (now ()) |+-----------------+| 201310 |+-----------------+1 row in set mysql>

Select Yearweek (Now ()-INTERVAL 1 days) calculates this week from Week 1

Select Yearweek (' 2015-02-02 11:12:00 '-INTERVAL 1 day)

############################

Excerpt from: http://hideto.javaeye.com/blog/255816

The first day of the current week:
Select Date_sub (Curdate (), INTERVAL WEEKDAY (Curdate ()) + 1 day)

The last day of the current week:
Select Date_sub (Curdate (), INTERVAL WEEKDAY (Curdate ())

The first day of the previous week:
Select Date_sub (Curdate (), INTERVAL WEEKDAY (Curdate ()) + 8 day)

Last day of the previous week:
Select Date_sub (Curdate (), INTERVAL WEEKDAY (Curdate ()) + 2 day)

First two week of the day:
Select Date_sub (Curdate (), INTERVAL WEEKDAY (Curdate ()) + day)

Last day of the first two week:
Select Date_sub (Curdate (), INTERVAL WEEKDAY (Curdate ()) + 9 day)
The first day of the current month:
SELECT concat (Date_format (Last_day (now ()), '%y-%m-'), ' 01′ '

The last day of the current month:
SELECT Last_day (now ())

First day of the previous month:
SELECT concat (Date_format (Last_day (now () –interval 1 month), '%y-%m-'), ' 01′ ')

Last day of the previous month:
SELECT Last_day (now () –interval 1 month)

First day of the previous two month:
SELECT concat (Date_format (Last_day (Now () –interval 2 month), '%y-%m-'), ' 01′ ')

Last day of the first two month:
SELECT Last_day (Now () –interval 2 month)
The first day of the current quarter:
Select Concat (Date_format (Last_day (Makedate (EXTRACT (year from Curdate ()), 1) + Interval QUARTER (curdate ()) *3-3 month), '%y-%m-'), ' 01′ ')

The last day of the current quarter:
Select Last_day (Makedate (EXTRACT (year from Curdate ()), 1) + Interval QUARTER (curdate ()) *3-1 month)

The first day of the previous quarter:
Select Concat (Date_format (Last_day (Makedate (EXTRACT (year from Curdate ()), 1) + Interval QUARTER (curdate ()) *3-6 month), '%y-%m-'), ' 01′ ')

Last day of the previous quarter:
Select Last_day (Makedate (EXTRACT (year from Curdate ()), 1) + Interval QUARTER (curdate ()) *3-4 month)

First two quarter of the day:
Select Concat (Date_format (Last_day (Makedate (EXTRACT (year from Curdate ()), 1) + Interval QUARTER (curdate ()) *3-9 month), '%y-%m-'), ' 01′ ')

Last day of the first two quarter:
Select Last_day (Makedate (EXTRACT (year from Curdate ()), 1) + Interval QUARTER (curdate ()) *3-7 month)

Unix timestamp converted to date function: From_unixtime ()

1. Select From_unixtime (1156219870);

Date conversion to UNIX timestamp function: Unix_timestamp ()

1. Select unix_timestamp (' 2006-11-04 12:23:00′);

########################

Today

SELECT * FROM table name where To_days (Time field name) = To_days (now ());

Yesterday

SELECT * FROM table name WHERE to_days (now ())-To_days (Time field name) <= 1

7 days

SELECT * FROM table name where Date_sub (Curdate (), INTERVAL 7 day) <= Date (Time field name)

Nearly 30 days

SELECT * FROM table name where Date_sub (Curdate (), INTERVAL Day) <= Date (Time field name)

Month

SELECT * FROM table name WHERE date_format (Time field name, '%y%m ') = Date_format (Curdate (), '%y%m ')

Last month

SELECT * FROM table name WHERE Period_diff (Date_format (now (), '%y%m '), Date_format (Time field name, '%y%m ')) =1

#查询本季度数据
SELECT * from ' ht_invoice_information ' where QUARTER (create_date) =quarter (now ());
#查询上季度数据
SELECT * from ' ht_invoice_information ' where QUARTER (create_date) =quarter (Date_sub (now (), Interval 1 QUARTER));
#查询本年数据
SELECT * from ' Ht_invoice_information ' where year (create_date) =year (now ());
#查询上年数据
SELECT * from ' Ht_invoice_information ' where year (create_date) =year (Date_sub (now (), interval 1));

Querying data for the current week
SELECT Name,submittime from the 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;// A week abroad is from Sunday to Saturday to calculate the SELECT * from Ordersrecord WHERE yearweek (ordertime,1) =yearweek (Date_sub (Curdate (), Interval 7 day ), 1)

Querying data for the current month
Select Name,submittime from Enterprise where Date_format (Submittime, '%y-%m ') =date_format (now (), '%y-%m ')

Query data that is currently 6 months from now
Select Name,submittime from Enterprise where Submittime between Date_sub (now (), Interval 6 month) and now ();

Querying data for the previous 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 last month
And the first day of next month

where date (regdate) = Curdate ();

SELECT * FROM Test where year (regdate) =year (now ()) and month (regdate) =month (now ()) and Day (regdate) =day ( Now ())

SELECT Date (c_instime), Curdate ()
From ' T_score '
WHERE 1
LIMIT 0, 30

Excerpt from: http://blog.csdn.net/zzhongcy/article/details/43016685

MySQL Query today/this week/last week/this month/last month's data