MySQL query today, this week, last week, this month, last month, this quarter, last quarter, this year, last year data SQL

Recently, due to the need of work, need to statistics all kinds of report data, SQL statements are super long and super complex, now only summarize some more basic SQL statistics knowledge about time.

Now suppose you have an order data table like this:

1 CREATE TABLE order (2 ID int(11) unsigned NOT NULL AUTO_INCREMENT, 3 order_sn Varchar (50) CHARACTER SET UTf8 NOT NULL DEFAULT ” COMMENT ‘ Create_at int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘create time ‘, 5 Success_at int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘order completion time ‘, 6 creator_id varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT COMMENT 7 Creator_id varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT COMMENT 7 creator_id varchar(50) CHARACTER SET utf8 NOT NULL DEFAULT COMMENT 8 UNIQUE KEY uni_sn (order_sn), 10) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET= UTf8MB4 COLLATE= UTf8MB4_unicode_ci COMMENT=’ order table ‘; Now use the table above as an example to query the relevant data:

Query all order numbers completed today:

SELECT order_sn FROM order WHERE YEARWEEK(FROM_UNIXTIME(success_at,’%Y-%m-%d’)) = date_format(now(),’%Y-%m-%d’);

Query all completed order numbers of the current week:

SELECT order_sn FROM order WHERE YEARWEEK(FROM_UNIXTIME(success_at,’%Y-%m-%d’)) = YEARWEEK(now());

Query all order numbers completed last week:

SELECT order_sn FROM order WHERE YEARWEEK(FROM_UNIXTIME(success_at,’%Y-%m-%d’)) = YEARWEEK(now())-1;

Query all completed order numbers in the current month:

select order_sn from order where FROM_UNIXTIME(success_at,’%Y-%m’)=date_format(now(),’%Y-%m’);

Query all completed order numbers of last month:

select order_sn from order where FROM_UNIXTIME(success_at,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 month),’%Y-%m’);

Query order number completed 6 months from now:

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 all completed order numbers for this quarter:

select order_sn from order where QUARTER(FROM_UNIXTIME(success_at,’%Y-%m-%d’))=QUARTER(now());

Query all completed order numbers of last quarter:

select order_sn from order where QUARTER(FROM_UNIXTIME(success_at,’%Y-%m-%d’))=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

To query all completed order numbers for the current year:

select order_sn from order where YEAR(FROM_UNIXTIME(success_at,’%Y-%m-%d’))=YEAR(NOW());

Query all completed orders in last year:

select order_sn from order where year(FROM_UNIXTIME(success_at,’%Y-%m-%d’))=year(date_sub(now(),interval 1 year));