How to use the MySQL NOT EXISTS operator

Guido1971 · May 17, 2020, 4:20pm

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

m_hutley · May 17, 2020, 11:02pm

I think what you’re describing at this point is

SELECT start_date, end_date
FROM `calendar_recovery_interval_hour_2020`
WHERE start_date NOT IN(
  SELECT sdatetime 
  FROM `stable_2020`
)

m_hutley · May 18, 2020, 12:37pm

Oh sorry, I didnt look closely enough at your stable_2020 data. It’s not necessarily matching. Gotcha.

SELECT start_date,end_date 
FROM `calendar_recovery_interval_hour_2020` i
LEFT JOIN `stable_2020` s 
ON i.start_date = CAST(DATE_FORMAT(s.sdatetime,'%Y-%m-%d %h:00:00') AS DATETIME)
WHERE STUX IS NULL;

(The cast may actually be unnecessary, i haven’t tested.)

Guido1971 · May 18, 2020, 3:08pm

Many thanks for this help.

system · August 17, 2020, 10:08pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Select timeline in MySQL database Databases mysql	15	3522	April 23, 2020
Add and Sub time in MySQL Databases	4	1446	April 13, 2016
Date interval Databases	1	547	October 8, 2014
Double bookings problem mysql Databases	8	3694	September 25, 2010
Time/Date Query Databases	9	368	April 22, 2011

How to use the MySQL NOT EXISTS operator

Related topics