locked
SQL NOT BETWEEN syntax in query on MySQL RRS feed

  • Question

  • User1151703306 posted

    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 INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 06:00:00', '2020-04-14 06:59:00', '7');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 07:00:00', '2020-04-14 07:59:00', '8');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 08:00:00', '2020-04-14 08:59:00', '9');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 09:00:00', '2020-04-14 09:59:00', '10');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 10:00:00', '2020-04-14 10:59:00', '11');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 11:00:00', '2020-04-14 11:59:00', '12');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 12:00:00', '2020-04-14 12:59:00', '13');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 13:00:00', '2020-04-14 13:59:00', '14');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 14:00:00', '2020-04-14 14:59:00', '15');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 15:00:00', '2020-04-14 15:59:00', '16');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 16:00:00', '2020-04-14 16:59:00', '17');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 17:00:00', '2020-04-14 17:59:00', '18');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 18:00:00', '2020-04-14 18:59:00', '19');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 19:00:00', '2020-04-14 19:59:00', '20');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 20:00:00', '2020-04-14 20:59:00', '21');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 21:00:00', '2020-04-14 21:59:00', '22');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 22:00:00', '2020-04-14 22:59:00', '23');
    INSERT INTO `calendar_recovery_interval_hour_2020` VALUES ('2020-04-14 23:00:00', '2020-04-14 23:59:00', '24');
    Sunday, May 17, 2020 8:20 PM

Answers

  • User475983607 posted

    The problem is your general design.  Change the stable_2020 table to include the Id from calendar_recovery_interval_hour_2020.

    DROP TABLE IF EXISTS `calendar_recovery_interval_hour_2020`;
    CREATE TABLE `calendar_recovery_interval_hour_2020` (
      `start_date` datetime DEFAULT NULL,
      `end_date` datetime DEFAULT NULL,
      `calendar_ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`calendar_ID`)
    ) ENGINE=InnoDB CHARSET=latin1;
    DROP TABLE IF EXISTS `stable_2020`;
    CREATE TABLE `stable_2020` (
      `STUX` int(11) DEFAULT NULL,
      `sdatetime` datetime DEFAULT NULL,
      `calendar_ID` int(11) NOT 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;

    Then update your insert scripts.

    set @date  = '2020-04-14 01:09:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 1);
    	
    	
    set @date  = '2020-04-14 01:59:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 2);
    
    set @date  = '2020-04-14 02:02:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 3);
    
    set @date  = '2020-04-14 02:52:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 4);

    Now you can use a LEFT JOIN

    SELECT * 
    FROM calendar_recovery_interval_hour_2020 AS c
    	LEFT JOIN stable_2020 AS s ON c.calendar_ID = s.calendar_ID
    WHERE s.calendar_ID iS NULL;

    Given this post and your others, I recommend learning programming and design fundamental.  The problem you are having above is table design and normalization.  You did not follow standard rules and ended up with an overly complicated design.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 18, 2020 3:30 PM

All replies

  • User475983607 posted

    You are creating primary keys but never use the keys.  Anyway, this is very simple LEFT when using standard patterns and practices.  

    SELECT * 
    FROM calendar_recovery_interval_hour_2020 AS h
    	LEFT JOIN stable_2020 AS s ON h.sId = s.sId
    WHERE s.Id IS NULL
    	AND end_date BETWEEN '2020-04-14' AND '2020-04-15'

    Fix the stable_2020 insert script to include the calendar_recovery_interval_hour_2020 primary key.   You can get the primary key with a simple query.

    SELECT sId 
    FROM calendar_recovery_interval_hour_2020
    WHERE start_date >= @theDate AND end_date <= @theDate

    Monday, May 18, 2020 12:19 PM
  • User1151703306 posted

    Thanks for help.

    I've tried, but the output don't return the time slot correct

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

    On your query are missing the time slot

    In these time slot the table `stable_2020` don't have rows

    2020-04-14 00:00:00 2020-04-14 00:59:00 1 
    2020-04-14 03:00:00 2020-04-14 03:59:00 4
    mysql> SELECT
    	*
    FROM
    	calendar_recovery_interval_hour_2020 AS h
    LEFT JOIN stable_2020 AS s ON h.sId = s.sId
    WHERE
    	s.sId IS NULL
    AND end_date BETWEEN '2020-04-14'
    AND '2020-04-15';
    +---------------------+---------------------+-----+------+-----------+------+
    | start_date          | end_date            | sID | STUX | sdatetime | sID  |
    +---------------------+---------------------+-----+------+-----------+------+
    | 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |   5 | NULL | NULL      | NULL |
    | 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |   6 | NULL | NULL      | NULL |
    | 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |   7 | NULL | NULL      | NULL |
    | 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |   8 | NULL | NULL      | NULL |
    | 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |   9 | NULL | NULL      | NULL |
    | 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |  10 | NULL | NULL      | NULL |
    | 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |  11 | NULL | NULL      | NULL |
    | 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |  12 | NULL | NULL      | NULL |
    | 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |  13 | NULL | NULL      | NULL |
    | 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |  14 | NULL | NULL      | NULL |
    | 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |  15 | NULL | NULL      | NULL |
    | 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |  16 | NULL | NULL      | NULL |
    | 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |  17 | NULL | NULL      | NULL |
    | 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |  18 | NULL | NULL      | NULL |
    | 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |  19 | NULL | NULL      | NULL |
    | 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |  20 | NULL | NULL      | NULL |
    | 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |  21 | NULL | NULL      | NULL |
    | 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |  22 | NULL | NULL      | NULL |
    | 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |  23 | NULL | NULL      | NULL |
    | 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |  24 | NULL | NULL      | NULL |
    +---------------------+---------------------+-----+------+-----------+------+
    20 rows in set

    Monday, May 18, 2020 12:44 PM
  • User475983607 posted

    Golia

    I've tried, but the output don't return the time slot which are not recorded in the table `stable_2020`

    That's because you are not using standard programming practices.  You went through all the trouble of creating a calendar with primary keys but never take advantage of the calendar keys.  Why create primary keys if you are not going to use the keys? 

    The process is very simple.  The stable_2020 table should have a foreign key relationship with calendar_recovery_interval_hour_2020.  Always insert the primary key from calendar_recovery_interval_hour_2020 into stable_2020 when adding a records.  

    You can get the key with a simple query. 

    SELECT sId 
    FROM calendar_recovery_interval_hour_2020
    WHERE start_date >= @theDate AND end_date <= @theDate

    I would create a procedure to do the insert.

    Monday, May 18, 2020 12:59 PM
  • User1151703306 posted

    thanks 

    sorry I'm not sure if I understand correctly...

    mysql> TRUNCATE TABLE `calendar_recovery_table_time_slot_2020`;
    
    SELECT
    	sId
    FROM
    	calendar_recovery_interval_hour_2020
    WHERE
    	start_date >= @theDate
    AND end_date <= @theDate;
    
    INSERT IGNORE INTO `calendar_recovery_table_time_slot_2020` SELECT
    	h.start_date,
    	h.end_date,
    	NULL
    FROM
    	calendar_recovery_interval_hour_2020 AS h
    LEFT JOIN stable_2020 AS s ON h.sId = s.sId
    WHERE
    	s.sId IS NULL;
    
    SELECT
    	*
    FROM
    	`calendar_recovery_table_time_slot_2020`;
    
    
    Query OK, 0 rows affected
    
    Empty set
    
    Query OK, 20 rows affected
    Records: 20  Duplicates: 0  Warnings: 0
    
    +---------------------+---------------------+-----+
    | start_date          | end_date            | sID |
    +---------------------+---------------------+-----+
    | 2020-04-14 04:00:00 | 2020-04-14 04:59:00 |   1 |
    | 2020-04-14 05:00:00 | 2020-04-14 05:59:00 |   2 |
    | 2020-04-14 06:00:00 | 2020-04-14 06:59:00 |   3 |
    | 2020-04-14 07:00:00 | 2020-04-14 07:59:00 |   4 |
    | 2020-04-14 08:00:00 | 2020-04-14 08:59:00 |   5 |
    | 2020-04-14 09:00:00 | 2020-04-14 09:59:00 |   6 |
    | 2020-04-14 10:00:00 | 2020-04-14 10:59:00 |   7 |
    | 2020-04-14 11:00:00 | 2020-04-14 11:59:00 |   8 |
    | 2020-04-14 12:00:00 | 2020-04-14 12:59:00 |   9 |
    | 2020-04-14 13:00:00 | 2020-04-14 13:59:00 |  10 |
    | 2020-04-14 14:00:00 | 2020-04-14 14:59:00 |  11 |
    | 2020-04-14 15:00:00 | 2020-04-14 15:59:00 |  12 |
    | 2020-04-14 16:00:00 | 2020-04-14 16:59:00 |  13 |
    | 2020-04-14 17:00:00 | 2020-04-14 17:59:00 |  14 |
    | 2020-04-14 18:00:00 | 2020-04-14 18:59:00 |  15 |
    | 2020-04-14 19:00:00 | 2020-04-14 19:59:00 |  16 |
    | 2020-04-14 20:00:00 | 2020-04-14 20:59:00 |  17 |
    | 2020-04-14 21:00:00 | 2020-04-14 21:59:00 |  18 |
    | 2020-04-14 22:00:00 | 2020-04-14 22:59:00 |  19 |
    | 2020-04-14 23:00:00 | 2020-04-14 23:59:00 |  20 |
    +---------------------+---------------------+-----+
    20 rows in set
    

    Monday, May 18, 2020 1:12 PM
  • User475983607 posted

    The problem is your general design.  Change the stable_2020 table to include the Id from calendar_recovery_interval_hour_2020.

    DROP TABLE IF EXISTS `calendar_recovery_interval_hour_2020`;
    CREATE TABLE `calendar_recovery_interval_hour_2020` (
      `start_date` datetime DEFAULT NULL,
      `end_date` datetime DEFAULT NULL,
      `calendar_ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`calendar_ID`)
    ) ENGINE=InnoDB CHARSET=latin1;
    DROP TABLE IF EXISTS `stable_2020`;
    CREATE TABLE `stable_2020` (
      `STUX` int(11) DEFAULT NULL,
      `sdatetime` datetime DEFAULT NULL,
      `calendar_ID` int(11) NOT 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;

    Then update your insert scripts.

    set @date  = '2020-04-14 01:09:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 1);
    	
    	
    set @date  = '2020-04-14 01:59:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 2);
    
    set @date  = '2020-04-14 02:02:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 3);
    
    set @date  = '2020-04-14 02:52:00';
    
    SET @id = (SELECT calendar_ID 
    FROM calendar_recovery_interval_hour_2020
    WHERE @date between start_date and end_date);
    
    INSERT INTO `stable_2020` VALUES ('14', @date, @id, 4);

    Now you can use a LEFT JOIN

    SELECT * 
    FROM calendar_recovery_interval_hour_2020 AS c
    	LEFT JOIN stable_2020 AS s ON c.calendar_ID = s.calendar_ID
    WHERE s.calendar_ID iS NULL;

    Given this post and your others, I recommend learning programming and design fundamental.  The problem you are having above is table design and normalization.  You did not follow standard rules and ended up with an overly complicated design.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 18, 2020 3:30 PM