mysql之日期函数练习

建表语句

create table test(`datetime` varchar(255) ,`sum` varchar(255))ENGINE=innodb CHARSET=utf8;

 数据

insert into test(datetime,sum) values('2018-6-1','10');
insert into test(datetime,sum) values('2018-6-2','11');
insert into test(datetime,sum) values('2018-6-3','11');
insert into test(datetime,sum) values('2018-6-4','12');
insert into test(datetime,sum) values('2018-6-5','14');
insert into test(datetime,sum) values('2018-6-6','15');
insert into test(datetime,sum) values('2018-6-7','13');
insert into test(datetime,sum) values('2018-6-8','37');
insert into test(datetime,sum) values('2018-6-9','18');
insert into test(datetime,sum) values('2018-6-10','19');
insert into test(datetime,sum) values('2018-6-11','10');
insert into test(datetime,sum) values('2018-6-12','11');
insert into test(datetime,sum) values('2018-6-13','11');
insert into test(datetime,sum) values('2018-6-14','12');

题目：

 函数小练习  求每七天的平均数

最后结果：

2018-06-01~2018-06-07 12

2018-06-08~2018-06-14 15

SELECT
	CONCAT_ws(
		'~',
		DATE_ADD(
			'2018-6-1',
			INTERVAL floor(
				datediff(datetime, '2018-6-1') / 7
			) * 7 DAY
		),
		DATE_ADD(
			'2018-6-1',
			INTERVAL floor(
				datediff(datetime, '2018-6-1') / 7
			) * 7 + 6 DAY
		)
	) AS time,
	floor(avg(sum)) AS s
FROM
	test
GROUP BY
	time;