目录
测试表结构和数据
近一小时
SELECT * FROM TB1 WHERE insert_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)
指定起始时间、结束时间
SELECT * FROM tb1 WHERE insert_time > '2017-09' AND insert_time < '2020-10'
今天
SELECT * FROM tb1 WHERE TO_DAYS(insert_time) = to_days(NOW())
昨天
SELECT * FROM tb1 WHERE TO_DAYS(NOW()) - TO_DAYS(insert_time) = 1
近七天
SELECT * FROM tb1 WHERE TO_DAYS(NOW()) - TO_DAYS(insert_time) <= 7
#or
SELECT * FROM tb1 WHERE DATE(insert_time) >= DATE_SUB(NOW(), INTERVAL 7 DAY)
#or
SELECT * FROM tb1 WHERE insert_time BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
近30天
SELECT * FROM tb1 WHERE TO_DAYS(NOW()) - TO_DAYS(insert_time) <= 30
#or
SELECT * FROM tb1 WHERE DATE_SUB(NOW(), INTERVAL 30 DAY) <= DATE(insert_time)
#or
SELECT * FROM tb1 WHERE insert_time BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW()
本月
SELECT * FROM tb1 WHERE DATE_FORMAT(insert_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
#指定年月2020-09
SELECT * FROM tb1 WHERE DATE_FORMAT(insert_time, '%Y-%m') = DATE_FORMAT(NOW(), '2020-09')
上月
SELECT * FROM tb1 WHERE PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(insert_time, '%Y%m')) = 1
本年
SELECT * FROM tb1 WHERE YEAR(insert_time) = YEAR(NOW())
上年
SELECT * FROM tb1 WHERE YEAR(insert_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))
近一年
SELECT * FROM tb1 WHERE insert_time BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW()
测试表结构和数据
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb1
-- ----------------------------
DROP TABLE IF EXISTS `tb1`;
CREATE TABLE `tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`insert_time` datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 29 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of tb1
-- ----------------------------
INSERT INTO `tb1` VALUES (1, '2010-10-06 19:26:09');
INSERT INTO `tb1` VALUES (2, '2016-01-09 11:22:33');
INSERT INTO `tb1` VALUES (3, '2016-02-09 11:22:33');
INSERT INTO `tb1` VALUES (4, '2017-04-15 11:22:33');
INSERT INTO `tb1` VALUES (5, '2017-04-19 11:22:33');
INSERT INTO `tb1` VALUES (6, '2017-10-15 11:22:33');
INSERT INTO `tb1` VALUES (7, '2017-10-19 11:22:33');
INSERT INTO `tb1` VALUES (8, '2018-03-19 11:22:33');
INSERT INTO `tb1` VALUES (9, '2018-03-21 11:22:33');
INSERT INTO `tb1` VALUES (10, '2018-10-19 11:22:33');
INSERT INTO `tb1` VALUES (11, '2018-10-21 11:22:33');
INSERT INTO `tb1` VALUES (12, '2019-10-21 11:22:33');
INSERT INTO `tb1` VALUES (13, '2019-02-20 11:22:33');
INSERT INTO `tb1` VALUES (14, '2019-02-20 11:22:33');
INSERT INTO `tb1` VALUES (15, '2019-10-21 11:22:33');
INSERT INTO `tb1` VALUES (16, '2019-10-21 11:22:33');
INSERT INTO `tb1` VALUES (17, '2020-07-19 11:22:33');
INSERT INTO `tb1` VALUES (18, '2020-07-19 11:22:33');
INSERT INTO `tb1` VALUES (19, '2020-09-19 11:22:33');
INSERT INTO `tb1` VALUES (20, '2020-09-19 11:22:33');
INSERT INTO `tb1` VALUES (21, '2020-10-15 11:22:33');
INSERT INTO `tb1` VALUES (22, '2020-10-15 11:22:33');
INSERT INTO `tb1` VALUES (23, '2020-10-19 11:22:33');
INSERT INTO `tb1` VALUES (24, '2020-10-19 11:22:33');
INSERT INTO `tb1` VALUES (25, '2020-10-21 11:22:33');
INSERT INTO `tb1` VALUES (26, '2020-10-21 11:22:33');
INSERT INTO `tb1` VALUES (27, '2020-10-22 11:22:33');
INSERT INTO `tb1` VALUES (28, '2020-10-22 11:22:33');
SET FOREIGN_KEY_CHECKS = 1;
近七天，每天数据量统计
SELECT
a.date AS date
, IFNULL( count( b.id ), 0 ) count
FROM
(
SELECT
tb.date1 AS date
FROM
(
SELECT
DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ), '%Y-%m-%d' ) AS date1
FROM
( SELECT @cdate := DATE_ADD( '2021-02-03', INTERVAL + 1 DAY ) FROM user LIMIT 7 ) AS tb
) AS tb
) a
LEFT JOIN user AS b ON a.date = date_format( b.insert_time, '%Y-%m-%d' )
GROUP BY date
ORDER BY date DESC
OK.
