- 积分
- 56
- UID
- 23005
- 阅读权限
- 20
- 注册时间
- 2016-9-8
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 3
|
两张表:一张时间表,一张测试数据表:
tmp_table_begin_end(一个开始时间,一个结束时间)
tmp_data(id,datastr,datavalue)
1 2016-09-08 13:36:21 1111
2 2016-09-08 13:08:21 2222
3 2016-09-08 13:04:21 3333
4 2016-09-08 13:03:21 44444
首先是生成时间组:
call p_hello('2016-09-08 13:00:00','2016-09-08 14:00:00',5);
相关存储过程:
/*
首先给定两个时间段
比如说begintime,endtime,baseminute=5
咱们以5分钟作为一个间隔
根据begintime和endtime,咱们生成出这些时间节点
*/
create procedure p_hello
(
in begintime datetime,
in endtime datetime,
in baseminute int
)
BEGIN
declare diffMinute int;
declare repeatCount int;
declare nextbegintime datetime;
truncate table tmp_table_begin_end;
select TIMESTAMPDIFF(MINUTE,begintime,endtime) into diffMinute;
select CEILING(diffMinute/baseminute) into repeatCount;
set @i=1;
while @i<=repeatCount do
set nextbegintime=date_add(begintime, interval baseminute minute);
insert into tmp_table_begin_end(begintime,endtime) values (begintime,date_add(nextbegintime, interval -1 second));
set begintime=nextbegintime;
set @i=@i+1;
end WHILE;
END
然后我们开始测试数据:
call p_hello('2016-09-08 13:00:00','2016-09-08 14:00:00',5);
生成的时间节点为:
2016-09-08 13:00:00 2016-09-08 13:04:59
2016-09-08 13:05:00 2016-09-08 13:09:59
2016-09-08 13:10:00 2016-09-08 13:14:59
2016-09-08 13:15:00 2016-09-08 13:19:59
2016-09-08 13:20:00 2016-09-08 13:24:59
2016-09-08 13:25:00 2016-09-08 13:29:59
2016-09-08 13:30:00 2016-09-08 13:34:59
2016-09-08 13:35:00 2016-09-08 13:39:59
2016-09-08 13:40:00 2016-09-08 13:44:59
2016-09-08 13:45:00 2016-09-08 13:49:59
2016-09-08 13:50:00 2016-09-08 13:54:59
2016-09-08 13:55:00 2016-09-08 13:59:59
好了,然后一条测试sql:
select *,(select count(*) from tmp_data where datestr BETWEEN tmp_table_begin_end.begintime and tmp_table_begin_end.endtime) from tmp_table_begin_end
2016-09-08 13:00:00 2016-09-08 13:04:59 2
2016-09-08 13:05:00 2016-09-08 13:09:59 1
2016-09-08 13:10:00 2016-09-08 13:14:59 0
2016-09-08 13:15:00 2016-09-08 13:19:59 0
2016-09-08 13:20:00 2016-09-08 13:24:59 0
2016-09-08 13:25:00 2016-09-08 13:29:59 0
2016-09-08 13:30:00 2016-09-08 13:34:59 0
2016-09-08 13:35:00 2016-09-08 13:39:59 1
2016-09-08 13:40:00 2016-09-08 13:44:59 0
2016-09-08 13:45:00 2016-09-08 13:49:59 0
2016-09-08 13:50:00 2016-09-08 13:54:59 0
2016-09-08 13:55:00 2016-09-08 13:59:59 0
|
|