SQL: задача о рабочем времени

Пардон, забыл проверить на пустых диапазонах. Периоды выпадают.

Попытка №2

WITH
periods (id, start_time, stop_time) AS (
    VALUES
        (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-31 12:00:00'::timestamp, '2019-01-01 16:00:00'::timestamp)
),
holidays (date) AS (
    VALUES
        ('2018-01-01'::DATE),
        ('2018-01-02'::DATE),
        ('2018-01-03'::DATE),
        ('2018-01-04'::DATE),
        ('2018-01-05'::DATE),
        ('2018-01-08'::DATE),
        ('2018-02-23'::DATE),
        ('2018-03-08'::DATE),
        ('2018-03-09'::DATE),
        ('2018-04-30'::DATE),
        ('2018-05-01'::DATE),
        ('2018-05-02'::DATE),
        ('2018-05-09'::DATE),
        ('2018-06-11'::DATE),
        ('2018-06-12'::DATE),
        ('2018-11-05'::DATE),
        ('2018-12-31'::DATE),
        ('2019-01-01'::DATE),
        ('2019-01-02'::DATE),
        ('2019-01-03'::DATE),
        ('2019-01-04'::DATE),
        ('2019-01-07'::DATE),
        ('2019-01-08'::DATE),
        ('2019-03-08'::DATE),
        ('2019-05-01'::DATE),
        ('2019-05-02'::DATE),
        ('2019-05-03'::DATE),
        ('2019-05-09'::DATE),
        ('2019-05-10'::DATE),
        ('2019-06-12'::DATE),
        ('2019-11-04'::DATE)
),
weekdays (date) AS (
    VALUES
        ('2018-04-28'::DATE),
        ('2018-06-09'::DATE),
        ('2018-12-29'::DATE)
),
durations AS (
    SELECT
        p.id,
        tsrange(p.start_time, p.stop_time) * tsrange(g.time + '10 HOURS'::INTERVAL, g.time + '19 HOURS'::INTERVAL) AS range
    FROM
        periods AS p
        LEFT JOIN generate_series(date_trunc('DAY', p.start_time), date_trunc('DAY', p.stop_time), '1 DAY') AS g (time) ON (TRUE)
        LEFT JOIN holidays AS h ON (h.date = g.time::DATE)
        LEFT JOIN weekdays AS w ON (w.date = g.time::DATE)
    WHERE
        (extract(DOW FROM g.time)::INT % 6 != 0 OR w.date IS NOT NULL)
        AND
        h.date IS NULL
)
SELECT
    p.*,
    coalesce(sum(age(upper(d.range), lower(d.range))), '00:00') AS work_hrs
FROM
    periods AS p
    LEFT JOIN durations AS d USING (id)
GROUP BY
    1, 2, 3
ORDER BY
    1;

SQL: задача о рабочем времени

Вообще, в таких задачах неплохо сразу оговаривать таймзоны, а то мало ли что. ;-) Исходил из того, что на дворе Москва.

Получилось postgres-specific, зато компактно. И почти без костылей.

Попытка №1

WITH
periods (id, start_time, stop_time) AS (
    VALUES
        (1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), 
        (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), 
        (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp),
        (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp)
),
holidays (date) AS (
    VALUES
        ('2018-01-01'::DATE),
        ('2018-01-02'::DATE),
        ('2018-01-03'::DATE),
        ('2018-01-04'::DATE),
        ('2018-01-05'::DATE),
        ('2018-01-08'::DATE),
        ('2018-02-23'::DATE),
        ('2018-03-08'::DATE),
        ('2018-03-09'::DATE),
        ('2018-04-30'::DATE),
        ('2018-05-01'::DATE),
        ('2018-05-02'::DATE),
        ('2018-05-09'::DATE),
        ('2018-06-11'::DATE),
        ('2018-06-12'::DATE),
        ('2018-11-05'::DATE),
        ('2018-12-31'::DATE),
        ('2019-01-01'::DATE),
        ('2019-01-02'::DATE),
        ('2019-01-03'::DATE),
        ('2019-01-04'::DATE),
        ('2019-01-07'::DATE),
        ('2019-01-08'::DATE),
        ('2019-03-08'::DATE),
        ('2019-05-01'::DATE),
        ('2019-05-02'::DATE),
        ('2019-05-03'::DATE),
        ('2019-05-09'::DATE),
        ('2019-05-10'::DATE),
        ('2019-06-12'::DATE),
        ('2019-11-04'::DATE)
),
weekdays (date) AS (
    VALUES
        ('2018-04-28'::DATE),
        ('2018-06-09'::DATE),
        ('2018-12-29'::DATE)
),
durations AS (
    SELECT
        p.*,
        tsrange(p.start_time, p.stop_time) * tsrange(g.time + '10 HOURS'::INTERVAL, g.time + '19 HOURS'::INTERVAL) AS range
    FROM
        periods AS p
        LEFT JOIN generate_series(date_trunc('DAY', p.start_time), date_trunc('DAY', p.stop_time), '1 DAY') AS g (time) ON (TRUE)
        LEFT JOIN holidays AS h ON (h.date = g.time::DATE)
        LEFT JOIN weekdays AS w ON (w.date = g.time::DATE)
    WHERE
        (extract(DOW FROM g.time)::INT % 6 != 0 OR w.date IS NOT NULL)
        AND
        h.date IS NULL
)
SELECT
    d.id,
    d.start_time,
    d.stop_time,
    coalesce(sum(age(upper(d.range), lower(d.range))), '00:00') AS work_hrs
FROM
    durations AS d
GROUP BY
    1, 2, 3
ORDER BY
    1;

Как выглядит работа PHP программиста в Швеции

50k многие дают. Вот выше взять — уже надо быть немного монстром.