Datensätze, die sich zeitlich 4 mal überlappen, MySQL

Datensätze, die sich zeitlich 4 mal überlappen

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

## Finde die Datensätze, die sich zeitlich 4 mal überlappen
## Ursprung: https://www.php.de/forum/webentwicklung/php-einsteiger/1505252-vergleichen-unbeschr%C3%A4nkt-vieler-zeitspannen-auf-%C3%BCberschneidung

## Zeittabelle

create table if not exists zeitdaten (
  id           mediumint not null auto_increment,
  datum        timestamp,                            ## Datzn und Uhrzeit 
  dauer_h      mediumint,                            ## Dauer in Stunden
  benutzer     varchar(20),                          ## Benutzer / user, nur so
  PRIMARY KEY  (id)
);

## Zeitdaten
insert into zeitdaten (datum, dauer_h, benutzer)
values (STR_TO_DATE('2017-05-05 02:20pm', '%Y-%m-%d %h:%i%p'), 3, 'u1'),
       (STR_TO_DATE('2017-05-05 04:25pm', '%Y-%m-%d %h:%i%p'), 2, 'u1'),
       (STR_TO_DATE('2017-05-05 05:15pm', '%Y-%m-%d %h:%i%p'), 1, 'u2'),
       (STR_TO_DATE('2017-05-05 05:20pm', '%Y-%m-%d %h:%i%p'), 1, 'u3'),
       (STR_TO_DATE('2017-05-05 06:20pm', '%Y-%m-%d %h:%i%p'), 1, 'u3'),       
       (STR_TO_DATE('2017-05-05 08:25pm', '%Y-%m-%d %h:%i%p'), 3, 'u3'),
       (STR_TO_DATE('2017-05-05 09:26pm', '%Y-%m-%d %h:%i%p'), 2, 'u1'),
       (STR_TO_DATE('2017-05-05 09:34pm', '%Y-%m-%d %h:%i%p'), 1, 'u2'),
       (STR_TO_DATE('2017-05-05 10:10pm', '%Y-%m-%d %h:%i%p'), 3, 'u2'),
       (STR_TO_DATE('2017-05-06 01:30pm', '%Y-%m-%d %h:%i%p'), 3, 'u2'),
       (STR_TO_DATE('2017-05-07 11:40pm', '%Y-%m-%d %h:%i%p'), 1, 'u1');

## Datensätze, die sich 4x überlappen (in mysql ohne with clause, ohne range type)
select z.*, DATE_ADD(z.datum, INTERVAL z.dauer_h HOUR) as datum_ende from zeitdaten z order by datum;

select z1.id as id1, z2.id as id2, z3.id as id3, z4.id as id4, z1.datum, z1.dauer_h, z1.datum_ende
  from ( select z.*, DATE_ADD(z.datum, INTERVAL z.dauer_h HOUR) as datum_ende from zeitdaten z) z1,
       ( select z.*, DATE_ADD(z.datum, INTERVAL z.dauer_h HOUR) as datum_ende from zeitdaten z) z2,
       ( select z.*, DATE_ADD(z.datum, INTERVAL z.dauer_h HOUR) as datum_ende from zeitdaten z) z3,
       ( select z.*, DATE_ADD(z.datum, INTERVAL z.dauer_h HOUR) as datum_ende from zeitdaten z) z4
 where
       z2.datum between z1.datum and z1.datum_ende and z1.id != z2.id
   and
       z3.datum between z1.datum and z1.datum_ende and z3.id != z1.id
       and
       z3.datum between z2.datum and z2.datum_ende and z3.id != z2.id
   and
       z4.datum between z1.datum and z1.datum_ende and z4.id != z1.id
       and
       z4.datum between z2.datum and z2.datum_ende and z4.id != z2.id
       and
       z4.datum between z3.datum and z3.datum_ende and z4.id != z3.id;

drop table zeitdaten;


run \| edit \| history \| help	0

λ

                                      .NET NoSQL database for rapid development