在开始和结束时间之间获得一分钟的间隔

3lxsmp7m  于 2021-06-21  发布在  Mysql
关注(0)|答案(2)|浏览(343)
Id    |    starttime        |    endtime          |
 1    |    1999-05-07 15:00 |    1999-05-07 16:45 |

从开始时间到结束时间的间隔是1分钟。
所以我想要这样的东西 15:01 , 15:02 .
请任何帮助,将感谢新的sql和尝试学习它,我可以最好的。谢谢
注:
我不要一分钟的时间。我想得到从开始到结束的每一分钟,我需要它来计算用户登录游戏的每一分钟,如果这是有意义的

iyr7buue

iyr7buue1#

你可能需要使用循环,
首先

select TIMESTAMPDIFF(minute,starttime,endtime)  from table1

给你105英镑
计算两次之间的分钟数

SELECT date_format(date_add(starttime,interval 1 minute),'%H:%i') from table1
SELECT date_format(date_add(starttime,interval 2 minute),'%H:%i') from table1
SELECT date_format(date_add(starttime,interval 3 minute),'%H:%i') from table1

循环这个直到105次,间隔时间递增

qvk1mo1f

qvk1mo1f2#

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

演示
https://www.db-fiddle.com/f/nge35tstwyd3plsw6x1te4/2

相关问题