locked
How to find events the next day and after midnight in MySQL RRS feed

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

    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