MySQL queries the SQL statements used for data from today, this week, last week, this month, last month, this quarter, last quarter, and last year.

MySQL queries the SQL statements used for data from today, this week, last week, this month, last month, this quarter, last quarter, and last year.

Recently, due to work requirements, various report data needs to be collected. SQL statements are written in ultra-long and ultra-complex ways. Now, we only summarize some basic SQL statistics on time.

Assume that there is an order data table:

Create table 'order' ('id' int (11) unsigned not null AUTO_INCREMENT, 'order _ sn 'varchar (50) character set utf8 not null default ''comment' order number, ensure that the unique ', 'create _ at' int (11) not null defaults '0' comment' creation time', 'success _ at' int (11) not null default '0' comment' order completion time', 'creator _ id' varchar (50) character set utf8 not null default ''comment' order creator ', primary key ('id'), unique key 'uni _ sn '('order _ sn '),) ENGINE = InnoDB AUTO_INCREMENT = 1 default charset = utf8mb4 COLLATE = utf8mb4_unicode_ci COMMENT = 'order table ';

Now, take the preceding table as an example to query related data:

Query the number of all completed orders today:

SELECT 'order _ sn 'FROM 'order' where yearweek (FROM_UNIXTIME (success_at,' % Y-% m-% D') = date_format (now (), '% Y-% m-% D ');

Query the number of all completed orders for the current week:

SELECT 'order _ sn 'FROM 'order' where yearweek (FROM_UNIXTIME (success_at,' % Y-% m-% D') = YEARWEEK (now ());

Query the number of all completed orders last week:

SELECT 'order _ sn 'FROM 'order' where yearweek (FROM_UNIXTIME (success_at,' % Y-% m-% D') = YEARWEEK (now ()-1;

Query the number of all completed orders in the current month:

Select 'order _ sn 'from 'order' where FROM_UNIXTIME (success_at,' % Y-% m') = date_format (now (), '% Y-% m ');

Query the number of all completed orders in the previous month:

Select 'order _ sn 'from 'order' where FROM_UNIXTIME (success_at,' % Y-% m') = date_format (DATE_SUB (curdate (), INTERVAL 1 month ), '% Y-% m ');

Query the number of orders completed six months ago:

Select 'order _ sn 'from 'order' where FROM_UNIXTIME (success_at,' % Y-% m-% d % H: % I: % s ') between date_sub (now (), interval 6 month) and now ();

Query the number of all completed orders for this quarter:

Select 'order _ sn 'from 'order' where QUARTER (FROM_UNIXTIME (success_at,' % Y-% m-% D') = QUARTER (now ());

Query the number of all completed orders in the previous quarter:

Select 'order _ sn 'from 'order' where QUARTER (FROM_UNIXTIME (success_at,' % Y-% m-% D') = QUARTER (DATE_SUB (now (), interval 1 QUARTER ));

Query the number of all completed orders this year:

Select 'order _ sn 'from 'order' where YEAR (FROM_UNIXTIME (success_at,' % Y-% m-% D') = YEAR (NOW ());
 
Query the number of all completed orders in the previous year:

Select 'order _ sn 'from 'order' where year (FROM_UNIXTIME (success_at,' % Y-% m-% D') = year (date_sub (now (), interval 1 year ));