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 day 

I'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');

Answer 1

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..

Answer 2

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