Hi gurus,
I have problem with MySQL database
On the table calendar_recovery_interval_hour_2020
I divided the days into time slots of one hour each, e.g. 2020-04-14
+---------------------+---------------------+-----+
| start_date | end_date | sID |
+---------------------+---------------------+-----+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 | 1 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 | 2 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 | 3 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 | 4 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 | 5 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 | 6 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 | 7 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 | 8 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 | 9 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 | 10 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 | 11 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 | 12 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 | 13 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 | 14 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 | 15 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 | 16 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 | 17 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 | 18 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 | 19 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 | 20 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 | 21 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 | 22 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 | 23 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 | 24 |
+---------------------+---------------------+-----+
24 rows in set
Now I need to find in a second table stable_2020
for the time slots missing with respect to table calendar_recovery_interval_hour_2020
+------+---------------------+-----+
| STUX | sdatetime | sID |
+------+---------------------+-----+
| 14 | 2020-04-14 01:09:00 | 1 |
| 14 | 2020-04-14 01:59:00 | 2 |
| 14 | 2020-04-14 02:02:00 | 3 |
| 14 | 2020-04-14 02:52:00 | 4 |
+------+---------------------+-----+
4 rows in set
This the tutorial
On this example I need the output below, because on stable_2020
I have four rows
Two rows on time slot
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 | 2 |
Other two rows on time slot
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 | 3 |
output required
+---------------------+---------------------+
| start_date | end_date |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
The code I’ve tried below without success
mysql> SELECT DISTINCT
t.start_date,
t.end_date
FROM
`calendar_recovery_interval_hour_2020` t,
`stable_2020` m
WHERE
m.`sdatetime` NOT BETWEEN Cast(t.start_date AS DateTime)
AND Cast(t.end_date AS DateTime);
+---------------------+---------------------+
| start_date | end_date |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
24 rows in set
And
mysql> SELECT
start_date,
end_date
FROM
`calendar_recovery_interval_hour_2020` t
WHERE
EXISTS (
SELECT
1
FROM
`stable_2020` m
WHERE
m.`sdatetime` NOT BETWEEN t.start_date
AND t.end_date
ORDER BY
m.`sdatetime` DESC
);
+---------------------+---------------------+
| start_date | end_date |
+---------------------+---------------------+
| 2020-04-14 00:00:00 | 2020-04-14 00:59:00 |
| 2020-04-14 01:00:00 | 2020-04-14 01:59:00 |
| 2020-04-14 02:00:00 | 2020-04-14 02:59:00 |
| 2020-04-14 03:00:00 | 2020-04-14 03:59:00 |
| 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |
| 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |
| 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |
| 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |
| 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |
| 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |
| 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |
| 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |
| 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |
| 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |
| 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |
| 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |
| 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |
| 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |
| 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |
| 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |
| 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |
| 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |
| 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |
| 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |
+---------------------+---------------------+
24 rows in set
My structure table below
DROP TABLE IF EXISTS `stable_2020`;
CREATE TABLE `stable_2020` (
`STUX` int(11) DEFAULT NULL,
`sdatetime` datetime DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`),
UNIQUE KEY `ukey` (`STUX`,`sdatetime`) USING BTREE,
KEY `sdatetime` (`sdatetime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of stable_2020
-- ----------------------------
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 01:09:00', '1');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 01:59:00', '2');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 02:02:00', '3');
INSERT INTO `stable_2020` VALUES ('14', '2020-04-14 02:52:00', '4');
DROP TABLE IF EXISTS `calendar_recovery_interval_hour_2020`;
CREATE TABLE `calendar_recovery_interval_hour_2020` (
`start_date` datetime DEFAULT NUL
`end_date` datetime DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`)
) ENGINE=InnoDB CHARSET=latin1;
-- ----------------------------
-- Records of calendar_recovery_interval_hour_2020
-- ----------------------------
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 00:00:00', '2020-04-14 00:59:00', '1');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 01:00:00', '2020-04-14 01:59:00', '2');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 02:00:00', '2020-04-14 02:59:00', '3');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 03:00:00', '2020-04-14 03:59:00', '4');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 04:00:00', '2020-04-14 04:59:00', '5');
INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 05:00:00', '2020-04-14 05:59:00', '6');
INSERT