Answered by:
How to find events the next day and after midnight in MySQL

Question
-
User1151703306 posted
Hi
I need your help
the day was divided into four time slots
00:00:00-06:00:00 06:00:00-12:00:00 12:00:00-18:00:00 18:00:00-24:00:00
for each of these time slots I need to search the rows in the database table MySQL between for start hour and end hour
21:00:00-09:00:00 03:00:00-15:00:00 09:00:00-21:00:00 15:00:00-03:00:00
that is
(-3h) 00:00:00-06:00:00 (+3h) (-3h) 06:00:00-12:00:00 (+3h) (-3h) 12:00:00-18:00:00 (+3h) (-3h) 18:00:00-24:00:00 (+3h)
the
`xtable`
is truncated each day and recompile with only the events of previous dayI'v tried this query for time slot `(-3h) 18:00:00-24:00:00 (+3h)`
mysql> SELECT * FROM `xtable` WHERE xDate IN ('2020-05-20') AND ( xHourMinute >= '15:00:00' OR xHourMinute <= '03:00:00' ); +----------+------------+-------------+----+ | xID | xDate | xHourMinute | ID | +----------+------------+-------------+----+ | 20006468 | 2020-05-20 | 00:22:19 | 1 | | 20006470 | 2020-05-20 | 00:36:40 | 2 | | 20006472 | 2020-05-20 | 00:48:04 | 3 | | 20006474 | 2020-05-20 | 00:52:06 | 4 | | 20006476 | 2020-05-20 | 00:58:15 | 5 | | 20006486 | 2020-05-20 | 01:05:00 | 6 | | 20006478 | 2020-05-20 | 01:14:15 | 7 | | 20006482 | 2020-05-20 | 01:25:35 | 8 | | 20006484 | 2020-05-20 | 02:12:01 | 9 | | 20006488 | 2020-05-20 | 02:52:27 | 10 | | 20006552 | 2020-05-20 | 15:22:20 | 27 | | 20006556 | 2020-05-20 | 15:55:00 | 28 | | 20006562 | 2020-05-20 | 16:44:00 | 29 | | 20006564 | 2020-05-20 | 18:22:00 | 30 | | 20006566 | 2020-05-20 | 19:25:00 | 31 | | 20006568 | 2020-05-20 | 20:22:58 | 32 | +----------+------------+-------------+----+ 16 rows in set
But the return is wrong because they were extracted these row which are the ones with date `2020-05-20` and not `2020-05-21` remember `24:00:00 (+3h)` this is the next day...
+----------+------------+-------------+----+ | xID | xDate | xHourMinute | ID | +----------+------------+-------------+----+ | 20006468 | 2020-05-20 | 00:22:19 | 1 | | 20006470 | 2020-05-20 | 00:36:40 | 2 | | 20006472 | 2020-05-20 | 00:48:04 | 3 | | 20006474 | 2020-05-20 | 00:52:06 | 4 | | 20006476 | 2020-05-20 | 00:58:15 | 5 | | 20006486 | 2020-05-20 | 01:05:00 | 6 | | 20006478 | 2020-05-20 | 01:14:15 | 7 | | 20006482 | 2020-05-20 | 01:25:35 | 8 | | 20006484 | 2020-05-20 | 02:12:01 | 9 | | 20006488 | 2020-05-20 | 02:52:27 | 10 | +----------+------------+-------------+----+
the correct return should be
+----------+------------+-------------+----+ | xID | xDate | xHourMinute | ID | +----------+------------+-------------+----+ | 20006552 | 2020-05-20 | 15:22:20 | 27 | | 20006556 | 2020-05-20 | 15:55:00 | 28 | | 20006562 | 2020-05-20 | 16:44:00 | 29 | | 20006564 | 2020-05-20 | 18:22:00 | 30 | | 20006566 | 2020-05-20 | 19:25:00 | 31 | | 20006568 | 2020-05-20 | 20:22:58 | 32 | +----------+------------+-------------+----+
Can you help me, please?
My structure table below
DROP TABLE IF EXISTS `xtable`; CREATE TABLE `xtable` ( `xID` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `xDate` date DEFAULT NULL, `xHourMinute` char(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL, `ID` int(10) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`ID`), UNIQUE KEY `xID` (`xID`), KEY `xDate` (`xDate`), KEY `xHourMinute` (`xHourMinute`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of xtable -- ---------------------------- INSERT INTO `xtable` VALUES ('20006468', '2020-05-20', '00:22:19', '1'); INSERT INTO `xtable` VALUES ('20006470', '2020-05-20', '00:36:40', '2'); INSERT INTO `xtable` VALUES ('20006472', '2020-05-20', '00:48:04', '3'); INSERT INTO `xtable` VALUES ('20006474', '2020-05-20', '00:52:06', '4'); INSERT INTO `xtable` VALUES ('20006476', '2020-05-20', '00:58:15', '5'); INSERT INTO `xtable` VALUES ('20006486', '2020-05-20', '01:05:00', '6'); INSERT INTO `xtable` VALUES ('20006478', '2020-05-20', '01:14:15', '7'); INSERT INTO `xtable` VALUES ('20006482', '2020-05-20', '01:25:35', '8'); INSERT INTO `xtable` VALUES ('20006484', '2020-05-20', '02:12:01', '9'); INSERT INTO `xtable` VALUES ('20006488', '2020-05-20', '02:52:27', '10'); INSERT INTO `xtable` VALUES ('20006492', '2020-05-20', '03:05:34', '12'); INSERT INTO `xtable` VALUES ('20006494', '2020-05-20', '04:29:59', '13'); INSERT INTO `xtable` VALUES ('20006500', '2020-05-20', '05:45:15', '14'); INSERT INTO `xtable` VALUES ('20006498', '2020-05-20', '05:45:16', '15'); INSERT INTO `xtable` VALUES ('20006496', '2020-05-20', '05:45:18', '16'); INSERT INTO `xtable` VALUES ('20006506', '2020-05-20', '07:09:07', '17'); INSERT INTO `xtable` VALUES ('20006504', '2020-05-20', '07:09:07', '18'); INSERT INTO `xtable` VALUES ('20006512', '2020-05-20', '07:56:44', '19'); INSERT INTO `xtable` VALUES ('20006510', '2020-05-20', '07:56:44', '20'); INSERT INTO `xtable` VALUES ('20006514', '2020-05-20', '08:13:02', '21'); INSERT INTO `xtable` VALUES ('20006518', '2020-05-20', '08:25:12', '22'); INSERT INTO `xtable` VALUES ('20006536', '2020-05-20', '09:51:28', '23'); INSERT INTO `xtable` VALUES ('20006538', '2020-05-20', '10:14:25', '24'); INSERT INTO `xtable` VALUES ('20006544', '2020-05-20', '11:40:42', '25'); INSERT INTO `xtable` VALUES ('20006548', '2020-05-20', '14:16:00', '26'); INSERT INTO `xtable` VALUES ('20006552', '2020-05-20', '15:22:20', '27'); INSERT INTO `xtable` VALUES ('20006556', '2020-05-20', '15:55:00', '28'); INSERT INTO `xtable` VALUES ('20006562', '2020-05-20', '16:44:00', '29'); INSERT INTO `xtable` VALUES ('20006564', '2020-05-20', '18:22:00', '30'); INSERT INTO `xtable` VALUES ('20006566', '2020-05-20', '19:25:00', '31'); INSERT INTO `xtable` VALUES ('20006568', '2020-05-20', '20:22:58', '32');
Thursday, May 21, 2020 3:06 PM
Answers
-
User475983607 posted
Seems like a design issue but you have not explained the requirement only the solution. Similar to your other post, I'm guessing you should set the time slot Id when the record is inserted or updated rather than trying to figure this out later. Or write a function that compares the date and time to the time slot table. Also it is not clear why you separate the date and the time..
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, May 21, 2020 4:43 PM
All replies
-
User475983607 posted
Seems like a design issue but you have not explained the requirement only the solution. Similar to your other post, I'm guessing you should set the time slot Id when the record is inserted or updated rather than trying to figure this out later. Or write a function that compares the date and time to the time slot table. Also it is not clear why you separate the date and the time..
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Thursday, May 21, 2020 4:43 PM -
User1151703306 posted
ok
I've now for union the date and the time
UPDATE xtable t SET t.xDate_xHourMinute = CONCAT( DATE_FORMAT(t.xDate, '%Y-%m-%d'), ' ', DATE_FORMAT(t.xHourMinute, '%H:%i:%s') );
Solved with
mysql> SELECT * FROM xtable t WHERE t.xDate_xHourMinute BETWEEN DATE_FORMAT( CONCAT( DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' ', '15:00:00' ), '%Y-%m-%d %H:%i:%s' ) AND DATE_FORMAT( CONCAT( DATE_ADD(CURDATE(), INTERVAL 1 DAY), ' ', '03:00:00' ), '%Y-%m-%d %H:%i:%s' ); +----------+------------+-------------+---------------------+----+ | xID | xDate | xHourMinute | xDate_xHourMinute | ID | +----------+------------+-------------+---------------------+----+ | 20006552 | 2020-05-20 | 15:22:20 | 2020-05-20 15:22:20 | 27 | | 20006556 | 2020-05-20 | 15:55:00 | 2020-05-20 15:55:00 | 28 | | 20006562 | 2020-05-20 | 16:44:00 | 2020-05-20 16:44:00 | 29 | | 20006564 | 2020-05-20 | 18:22:00 | 2020-05-20 18:22:00 | 30 | | 20006566 | 2020-05-20 | 19:25:00 | 2020-05-20 19:25:00 | 31 | | 20006568 | 2020-05-20 | 20:22:58 | 2020-05-20 20:22:58 | 32 | +----------+------------+-------------+---------------------+----+ 6 rows in set
many thanks for help
Thursday, May 21, 2020 7:48 PM