SELECT *,TIMESTAMPDIFF(MINUTE,starttime,endtime) FROM Table1
创建此函数
CREATE function getTimeAll(id1 int)
RETURNS VARCHAR(65535) DETERMINISTIC
BEGIN
DECLARE time1 varchar(65535);
DECLARE intrvlMin int;
DECLARE c int;
SET intrvlMin=(SELECT TIMESTAMPDIFF(MINUTE,starttime,endtime) FROM Table1 WHERE id = id1);
SET c=1;
SET time1 = '';
While c < intrvlMin
do
SET time1=CONCAT(time1,(SELECT date_format(date_add(starttime,interval c minute),'%H:%i') FROM Table1 WHERE id = id1),',');
SET c = c + 1;
end WHILE;
SET time1=CONCAT(time1,(SELECT date_format(date_add(starttime,interval c minute),'%H:%i') FROM Table1 WHERE id = id1));
RETURN time1;
END ;
那就跑吧
SELECT `Id`, `starttime`, `endtime`,getTimeAll(`Id`) AS Times FROM Table1;
输出
Id starttime endtime Times
1 1999-05-07 15:00:00 1999-05-07 16:45:00 15:01,15:02,15:03,15:04,15:05,15:06,15:07,15:08,15:09,15:10,15:11,15:12,15:13,15:14,15:15,15:16,15:17,15:18,15:19,15:20,15:21,15:22,15:23,15:24,15:25,15:26,15:27,15:28,15:29,15:30,15:31,15:32,15:33,15:34,15:35,15:36,15:37,15:38,15:39,15:40,15:41,15:42,15:43,15:44,15:45,15:46,15:47,15:48,15:49,15:50,15:51,15:52,15:53,15:54,15:55,15:56,15:57,15:58,15:59,16:00,16:01,16:02,16:03,16:04,16:05,16:06,16:07,16:08,16:09,16:10,16:11,16:12,16:13,16:14,16:15,16:16,16:17,16:18,16:19,16:20,16:21,16:22,16:23,16:24,16:25,16:26,16:27,16:28,16:29,16:30,16:31,16:32,16:33,16:34,16:35,16:36,16:37,16:38,16:39,16:40,16:41,16:42,16:43,16:44,16:45
2条答案
按热度按时间iyr7buue1#
你可能需要使用循环,
首先
给你105英镑
计算两次之间的分钟数
循环这个直到105次,间隔时间递增
qvk1mo1f2#
创建此函数
那就跑吧
输出
演示
https://www.db-fiddle.com/f/nge35tstwyd3plsw6x1te4/2