Calculating Night Shift duration - using SQL - or time tunnel Design pattern Part 1

How can you calculate the number of hours within the night shift (for example between 18-05) or during weekends (Saturday/Sunday)?

I don't want to use sub-quey as it not efficient - I create a Tunnel of working time with or without condition (as needed).

We create array of hours of worker shift time and weekend and then filter it on the desired hours / days appropriately.

It then calculate the cardinality (the length - how many hours / days) are fitting to the filter and then you have it ( I call this pattern the "time tunnel" - I'll write a dedicated blog BH on it:

The query (with data):

select name,start_sh,end_sh

,cardinality(filter(sequence(start_sh,end_sh,INTERVAL '1' hour),x->date_format(x,'%H')>'18' or date_format(x,'%H')<'05')) as nigh_shift,

cardinality(filter(sequence(start_sh,end_sh,INTERVAL '1' hour),x->date_format(x,'%W')='Saturday' or date_format(x,'%W')='Sunday')) as Weekend_hours

from

(values

('Mosh',date_parse('2019-12-15 10:00:12','%Y-%m-%d %H:%i:%s'),date_parse('2019-12-15 23:00:12','%Y-%m-%d %H:%i:%s')),

('Idan',date_parse('2019-08-05 15:02:47','%Y-%m-%d %H:%i:%s'),date_parse('2019-08-06 19:22:53','%Y-%m-%d %H:%i:%s')),

('Oshik',date_parse('2019-06-16 07:48:20','%Y-%m-%d %H:%i:%s'),date_parse('2019-06-16 13:30:42','%Y-%m-%d %H:%i:%s')),

('Shlomy',date_parse('2019-05-11 04:29:38','%Y-%m-%d %H:%i:%s'),date_parse('2019-05-11 12:40:54','%Y-%m-%d %H:%i:%s')),

('Hanna',date_parse('2019-11-15 14:53:21','%Y-%m-%d %H:%i:%s'),date_parse('2019-11-15 17:05:50','%Y-%m-%d %H:%i:%s')),

('Jhonathan',date_parse('2019-08-22 05:23:10','%Y-%m-%d %H:%i:%s'),date_parse('2019-08-23 10:48:51','%Y-%m-%d %H:%i:%s')),

('Menachem',date_parse('2019-09-30 03:23:09','%Y-%m-%d %H:%i:%s'),date_parse('2019-09-30 09:44:03','%Y-%m-%d %H:%i:%s')),

('Yossi',date_parse('2019-02-07 02:06:30','%Y-%m-%d %H:%i:%s'),date_parse('2019-02-07 17:43:02','%Y-%m-%d %H:%i:%s')),

('Yaniv',date_parse('2019-07-28 08:44:03','%Y-%m-%d %H:%i:%s'),date_parse('2019-07-29 12:43:28','%Y-%m-%d %H:%i:%s')),

('Jacky',date_parse('2019-03-14 06:39:50','%Y-%m-%d %H:%i:%s'),date_parse('2019-03-14 19:32:10','%Y-%m-%d %H:%i:%s')),

('Reuven',date_parse('2019-08-16 01:32:17','%Y-%m-%d %H:%i:%s'),date_parse('2019-08-17 05:13:44','%Y-%m-%d %H:%i:%s')),

('Baruch',date_parse('2019-01-25 18:21:22','%Y-%m-%d %H:%i:%s'),date_parse('2019-01-26 22:43:32','%Y-%m-%d %H:%i:%s')),

('Helena',date_parse('2019-12-17 07:27:46','%Y-%m-%d %H:%i:%s'),date_parse('2019-12-18 15:29:19','%Y-%m-%d %H:%i:%s')),

('Michcky',date_parse('2019-10-11 16:39:37','%Y-%m-%d %H:%i:%s'),date_parse('2019-10-12 21:43:17','%Y-%m-%d %H:%i:%s')),

('Tova',date_parse('2019-10-26 05:20:49','%Y-%m-%d %H:%i:%s'),date_parse('2019-10-26 12:45:58','%Y-%m-%d %H:%i:%s')),

('Sarha',date_parse('2019-12-23 02:10:04','%Y-%m-%d %H:%i:%s'),date_parse('2019-12-24 15:05:19','%Y-%m-%d %H:%i:%s')),

('Yochy',date_parse('2019-09-01 05:40:37','%Y-%m-%d %H:%i:%s'),date_parse('2019-09-01 10:42:37','%Y-%m-%d %H:%i:%s')),

('Elcha',date_parse('2019-04-23 13:39:46','%Y-%m-%d %H:%i:%s'),date_parse('2019-04-23 19:45:29','%Y-%m-%d %H:%i:%s')),

('Rivi',date_parse('2019-06-24 06:57:06','%Y-%m-%d %H:%i:%s'),date_parse('2019-06-24 12:17:43','%Y-%m-%d %H:%i:%s')),

('Bili',date_parse('2019-07-09 10:46:56','%Y-%m-%d %H:%i:%s'),date_parse('2019-07-10 17:36:36','%Y-%m-%d %H:%i:%s')),

('Eli',date_parse('2019-03-10 14:26:54','%Y-%m-%d %H:%i:%s'),date_parse('2019-03-10 17:36:13','%Y-%m-%d %H:%i:%s')),

('Gil',date_parse('2019-07-02 11:26:39','%Y-%m-%d %H:%i:%s'),date_parse('2019-07-03 22:36:55','%Y-%m-%d %H:%i:%s')))

t (name,start_sh,end_sh)

results

|name |start_sh |end_sh |nigh_shift |Weekend_hours|

|Mosh |2019-12-15 10:00:12|2019-12-15 23:00:12 | 5 | 14 |

|Idan |2019-08-05 15:02:47|2019-08-06 19:22:53 | 11 | 0 |

|Oshik |2019-06-16 07:48:20|2019-06-16 13:30:42 | 0 | 6 |

|Shlomy |2019-05-11 04:29:38|2019-05-11 12:40:54 | 1 | 9 |

|Hanna |2019-11-15 14:53:21|2019-11-15 17:05:50 | 0 | 0 |

|Jhonathan |2019-08-22 05:23:10|2019-08-23 10:48:51 | 10 | 0 |

|Menachem|2019-09-30 03:23:09|2019-09-30 09:44:03 | 2 | 0 |

|Yossi |2019-02-07 02:06:30|2019-02-07 17:43:02 | 3 | 0 |

|Yaniv |2019-07-28 08:44:03|2019-07-29 12:43:28 | 10 | 16 |

|Jacky |2019-03-14 06:39:50|2019-03-14 19:32:10 | 0 | 0 |

|Reuven |2019-08-16 01:32:17|2019-08-17 05:13:44 | 14 | 5 |

|Baruch |2019-01-25 18:21:22|2019-01-26 22:43:32 | 14 | 23 |

|Helena |2019-12-17 07:27:46|2019-12-18 15:29:19 | 10 | 0 |

|Michcky |2019-10-11 16:39:37|2019-10-12 21:43:17 | 13 | 22 |

|Tova |2019-10-26 05:20:49|2019-10-26 12:45:58 | 0 | 8 |

|Sarha |2019-12-23 02:10:04|2019-12-24 15:05:19 | 13 | 0 |

|Yochy |2019-09-01 05:40:37|2019-09-01 10:42:37 | 0 | 6 |

|Elcha |2019-04-23 13:39:46|2019-04-23 19:45:29 | 1 | 0 |

|Rivi |2019-06-24 06:57:06|2019-06-24 12:17:43 | 0 | 0 |

|Bili |2019-07-09 10:46:56|2019-07-10 17:36:36 | 10 | 0 |

|Eli |2019-03-10 14:26:54|2019-03-10 17:36:13 | 0 | 4 |

|Gil |2019-07-02 11:26:39|2019-07-03 22:36:55 | 14 | 0 |