gpt4 book ai didi

mysql - 如何在 MySQL 服务器中将最小(日期)和最大(日期)之间的小时分割为 4 小时板

转载 作者:行者123 更新时间:2023-11-29 06:27:04 30 4
gpt4 key购买 nike

我的 table :

CREATE TABLE `tbdata` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`PatientID` INT(10) NOT NULL,
`RecordDate` DATETIME NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

表格中的数据如下所示:

ID  PatientID RecordDate
132 172116 2019—11—15 13:43:45
133 172816 2019—11—15 13:43:55
134 172816 2019—11—15 13:44:06
135 172816 2019—11—15 13:44:16
136 172816 2019—11—15 13:44:27
137 172816 2019—11—15 13:44:38
138 172816 2019—11—15 13:44:48
139 172816 2019—11—15 13:44:59
140 172816 2019—11—15 13:45:09
141 172816 2019—11—15 13:45:20
142 172816 2019—11—15 13:45:31
143 172816 2019—11—15 13:45:41
144 172816 2019—11—15 13:45:52
145 172816 2019—11—15 13:46:02
146 172816 2019—11—15 13:46:13
147 172816 2019—11—15 13:46:24
148 172816 2019—11—15 13:46:35
149 172816 2019—11—15 13:46:46
150 172816 2019—11—15 13:46:56
151 172816 2019—11—15 13:47:07
152 172816 2019—11—15 13:47:19
153 172816 2019—11—15 13:47:30
154 172816 2019—11—15 13:47:43
155 172816 2019—11—15 13:47:54
156 172816 2019—11—15 13:48:04
157 172816 2019—11—15 13:48:15
158 172816 2019—11—15 14:48:25
159 172816 2019—11—15 17:48:36
160 172816 2019—11—15 18:48:47
161 172816 2019—11—15 20:48:57
162 172816 2019—11—15 22:49:08
163 172816 2019—11—15 23:49:18

所需结果如:

4 Hours slot :
start from min(RecordDate)
2019-11-15 13:00:00 - 2019-11-15 17:00:00
2019-11-15 17:00:00 - 2019-11-15 21:00:00
2019-11-15 21:00:00 - 2019-11-16 01:00:00
... till max(RecordDate)

最佳答案

我不知道它的表现如何,但它确实返回了你想要的:

编辑我已更新它以过滤掉表中没有数据的间隔。

**Schema (MySQL v8.0)**

CREATE TABLE `tbdata` (
`ID` INT(10) NOT NULL AUTO_INCREMENT,
`PatientID` INT(10) NOT NULL,
`RecordDate` DATETIME NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO tbdata (PatientID, RecordDate)
VALUES
(172116, '2019-11-15 13:43:45'),
(172816, '2019-11-15 13:43:55'),
(172816, '2019-11-15 13:44:06'),
(172816, '2019-11-15 13:44:16'),
(172816, '2019-11-15 13:44:27'),
(172816, '2019-11-15 13:44:38'),
(172816, '2019-11-15 13:44:48'),
(172816, '2019-11-15 13:44:59'),
(172816, '2019-11-15 13:45:09'),
(172816, '2019-11-15 13:45:20'),
(172816, '2019-11-15 13:45:31'),
(172816, '2019-11-15 13:45:41'),
(172816, '2019-11-15 13:45:52'),
(172816, '2019-11-15 13:46:02'),
(172816, '2019-11-15 13:46:13'),
(172816, '2019-11-15 13:46:24'),
(172816, '2019-11-15 13:46:35'),
(172816, '2019-11-15 13:46:46'),
(172816, '2019-11-15 13:46:56'),
(172816, '2019-11-15 13:47:07'),
(172816, '2019-11-15 13:47:19'),
(172816, '2019-11-15 13:47:30'),
(172816, '2019-11-15 13:47:43'),
(172816, '2019-11-15 13:47:54'),
(172816, '2019-11-15 13:48:04'),
(172816, '2019-11-15 13:48:15'),
(172816, '2019-11-15 14:48:25'),
(172816, '2019-11-15 17:48:36'),
(172816, '2019-11-15 18:48:47'),
(172816, '2019-11-15 20:48:57'),
(172816, '2019-11-15 22:49:08'),
(172816, '2019-11-15 23:49:18');

    DELIMITER //
CREATE PROCEDURE While_Loop()
BEGIN
SET @row_number = 0;

SELECT MIN(RecordDate), MAX(RecordDate)
INTO @min, @max
FROM tbdata;

SET @min = DATE_FORMAT(@min, "%Y-%m-%d %H:00:00");

SELECT CEIL(TIMESTAMPDIFF(HOUR, @min, @max)/4) INTO @number_of_intervals;

WHILE @row_number <= @number_of_intervals DO
SET @start_time = @min;
SET @end_time = DATE_ADD(@start_time, INTERVAL 4 HOUR);
SET @min = @end_time;
SET @row_number = @row_number + 1;

SELECT COUNT(PatientID) INTO @existent_slot FROM tbdata WHERE RecordDate BETWEEN @start_time and @end_time;

IF @existent_slot THEN
SELECT @start_time start_time, @end_time end_time;
END IF;
END WHILE;


END;

**Query #1**

CALL While_Loop();

| start_time | end_time |
| ------------------- | ------------------- |
| 2019-11-15 13:00:00 | 2019-11-15 17:00:00 |

| start_time | end_time |
| ------------------- | ------------------- |
| 2019-11-15 17:00:00 | 2019-11-15 21:00:00 |

| start_time | end_time |
| ------------------- | ------------------- |
| 2019-11-15 21:00:00 | 2019-11-16 01:00:00 |


您可能应该将结果存储在临时表中,而不是仅仅打印出来。

下面更新的代码对我有用。

DELIMITER $$

DROP PROCEDURE IF EXISTS `While_Loop`$$

CREATE PROCEDURE `While_Loop`()
BEGIN
DROP TEMPORARY TABLE IF EXISTS _tempTableTimeSlot;
CREATE TEMPORARY TABLE _tempTableTimeSlot(
starttime VARCHAR(100),
endtime VARCHAR(100)
);
SET @row_number = 0;
SELECT MIN(RecordDate), MAX(RecordDate)
INTO @min, @max
FROM tbdata;
SET @min = DATE_FORMAT(@min, "%Y-%m-%d %H:00:00");
SELECT CEIL(TIMESTAMPDIFF(HOUR, @min, @max)/4) INTO @number_of_intervals;
WHILE @row_number <= @number_of_intervals DO
SET @start_time = @min;
SET @end_time = DATE_ADD(@start_time, INTERVAL 4 HOUR);
SET @min = @end_time;
SET @row_number = @row_number + 1;

SELECT COUNT(PatientID) INTO @existent_slot FROM tbdata WHERE RecordDate BETWEEN @start_time AND @end_time;
IF @existent_slot THEN
INSERT INTO _tempTableTimeSlot(starttime,endtime) SELECT @start_time start_time1, @end_time end_time1;
END IF;
END WHILE;

SELECT * FROM _tempTable;
DROP TEMPORARY TABLE IF EXISTS _tempTableTimeSlot;
END$$

DELIMITER ;

关于mysql - 如何在 MySQL 服务器中将最小(日期)和最大(日期)之间的小时分割为 4 小时板,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58873608/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com