Рассчитать рабочее время между 2 датами в PostgreSQL

Я разрабатываю алгоритм с Postgres (PL/pgSQL), и мне нужно рассчитать количество рабочих часов между двумя отметками времени, учитывая, что выходные дни не работают, а остальные дни учитываются только с 8 утра до 15 вечера.

Примеры:

С 3 декабря в 14 час. 00 мин. до 4 декабря в 9:00 должно быть 2 часа:
```
3rd = 1, 4th = 1
```
С 3 декабря в 15:00 до 7 декабря в 8 утра должно быть указано 8 часов:
```
3rd = 0, 4th = 8, 5th = 0, 6th = 0, 7th = 0
```

Было бы здорово также рассмотреть часовые фракции.

Ответ 1

В соответствии с вашим вопросом рабочее время: Mo-Fr, 08: 00-15: 00.

Закругленные результаты

Только для двух заданных временных меток

Работа с единицами 1 час. Фракции игнорируются, поэтому не являются точными, но простыми:

SELECT count(*) AS work_hours
FROM   generate_series (timestamp '2013-06-24 13:30'
                      , timestamp '2013-06-24 15:29' - interval '1h'
                      , interval '1h') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00';

Функция generate_series() генерирует одну строку, если конец больше, чем начало и другая строка для каждого полного заданного интервала (1 час). Этот wold считает каждый час, введенный в. Чтобы игнорировать дробные часы, вычтите 1 час с конца. И не считайте часы, начинающиеся до 14:00.
Используйте шаблон поля ISODOW вместо DOW для EXTRACT() для упростить выражения. Возвращает 7 вместо 0 для воскресенья.
Простой (и очень дешевый) перевод на time позволяет легко определить квалификационные часы.
Фракции часа игнорируются, даже если фракции в начале и конце интервала будут составлять до часа или более.

Для всей таблицы

CREATE TEMP TABLE t (t_id int PRIMARY KEY, t_start timestamp, t_end timestamp);
INSERT INTO t VALUES 
  (1, '2009-12-03 14:00', '2009-12-04 09:00')
 ,(2, '2009-12-03 15:00', '2009-12-07 08:00')  -- examples in question
 ,(3, '2013-06-24 07:00', '2013-06-24 12:00')
 ,(4, '2013-06-24 12:00', '2013-06-24 23:00')
 ,(5, '2013-06-23 13:00', '2013-06-25 11:00')
 ,(6, '2013-06-23 14:01', '2013-06-24 08:59');  -- max. fractions at begin and end

Query:

SELECT t_id, count(*) AS work_hours
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '1h', interval '1h') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00'
GROUP  BY 1
ORDER  BY 1;

SQL Fiddle.

Более высокая точность

Чтобы получить больше точности, вы можете использовать меньшие единицы времени. 5-минутные фрагменты, например:

SELECT t_id, count(*) * interval '5 min' AS work_interval
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '5 min', interval '5 min') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:55'  -- 15.00 - interval '5 min'
GROUP  BY 1
ORDER  BY 1;

Чем меньше единица, тем выше стоимость.

Очиститель с `LATERAL` в Postgres 9.3 +

В сочетании с новой функцией LATERAL в Postgres 9.3 вышеуказанный запрос может быть записан как:

1-часовая точность:

SELECT t.t_id, h.work_hours
FROM   t
LEFT   JOIN LATERAL (
   SELECT count(*) AS work_hours
   FROM   generate_series (t.t_start, t.t_end - interval '1h', interval '1h') h
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= '08:00'
   AND    h::time <= '14:00'
   ) h ON TRUE
ORDER  BY 1;

5-минутная точность:

SELECT t.t_id, h.work_interval
FROM   t
LEFT   JOIN LATERAL (
   SELECT count(*) * interval '5 min' AS work_interval
   FROM   generate_series (t.t_start, t.t_end - interval '5 min', interval '5 min') h
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= '08:00'
   AND    h::time <= '14:55'
   ) h ON TRUE
ORDER  BY 1;

Это имеет дополнительное преимущество, что интервалы, содержащие нулевые рабочие часы, не исключаются из результата, как в приведенных выше версиях.

Подробнее о LATERAL:

Точные результаты

Postgres 8.4 +

Или вы имеете дело с началом и окончанием временного интервала отдельно, чтобы получить точные результаты на микросекунду. Делает запрос более сложным, но более дешевым и точным:

WITH var AS (SELECT '08:00'::time  AS v_start
                  , '15:00'::time  AS v_end)
SELECT t_id
     , COALESCE(h.h, '0')  -- add / subtract fractions
       - CASE WHEN EXTRACT(ISODOW FROM t_start) < 6
               AND t_start::time > v_start
               AND t_start::time < v_end
         THEN t_start - date_trunc('hour', t_start)
         ELSE '0'::interval END
       + CASE WHEN EXTRACT(ISODOW FROM t_end) < 6
               AND t_end::time > v_start
               AND t_end::time < v_end
         THEN t_end - date_trunc('hour', t_end)
         ELSE '0'::interval END                 AS work_interval
FROM   t CROSS JOIN var
LEFT   JOIN (  -- count full hours, similar to above solutions
   SELECT t_id, count(*)::int * interval '1h' AS h
   FROM  (
      SELECT t_id, v_start, v_end
           , generate_series (date_trunc('hour', t_start)
                            , date_trunc('hour', t_end) - interval '1h'
                            , interval '1h') AS h
      FROM   t, var
      ) sub
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= v_start
   AND    h::time <= v_end - interval '1h'
   GROUP  BY 1
   ) h USING (t_id)
ORDER  BY 1;

SQL Fiddle.

Postgres 9.2+ с `tsrange`

Новые типы диапазонов предлагают более элегантное решение для точных результатов в сочетании с оператором переадресации *:

Простая функция для диапазонов времени, охватывающих только один день:

CREATE OR REPLACE FUNCTION f_worktime_1day(_start timestamp, _end timestamp)
  RETURNS interval AS
$func$  -- _start & _end within one calendar day! - you may want to check ...
SELECT CASE WHEN extract(ISODOW from _start) < 6 THEN (
   SELECT COALESCE(upper(h) - lower(h), '0')
   FROM  (
      SELECT tsrange '[2000-1-1 08:00, 2000-1-1 15:00)' -- hours hard coded
           * tsrange( '2000-1-1'::date + _start::time
                    , '2000-1-1'::date + _end::time ) AS h
      ) sub
   ) ELSE '0' END
$func$  LANGUAGE sql IMMUTABLE;

Если ваши диапазоны никогда не охватывают несколько дней, все, что вам нужно.
Else, используйте эту функцию-обертку для работы с любым интервалом:

CREATE OR REPLACE FUNCTION f_worktime(_start timestamp
                                    , _end timestamp
                                    , OUT work_time interval) AS
$func$
BEGIN
   CASE _end::date - _start::date  -- spanning how many days?
   WHEN 0 THEN                     -- all in one calendar day
      work_time := f_worktime_1day(_start, _end);
   WHEN 1 THEN                     -- wrap around midnight once
      work_time := f_worktime_1day(_start, NULL)
                +  f_worktime_1day(_end::date, _end);
   ELSE                            -- multiple days
      work_time := f_worktime_1day(_start, NULL)
                +  f_worktime_1day(_end::date, _end)
                + (SELECT count(*) * interval '7:00'  -- workday hard coded!
                   FROM   generate_series(_start::date + 1
                                        , _end::date   - 1, '1 day') AS t
                   WHERE  extract(ISODOW from t) < 6);
   END CASE;
END
$func$  LANGUAGE plpgsql IMMUTABLE;

Вызов:

SELECT t_id, f_worktime(t_start, t_end) AS worktime
FROM   t
ORDER  BY 1;

SQL Fiddle.

Ответ 2

Как насчет этого: создайте небольшую таблицу с 24 * 7 строками, по одной строке для каждого часа в неделю.

CREATE TABLE hours (
  hour timestamp not null,
  is_working boolean not null
);

INSERT INTO hours (hour, is_working) VALUES
 ('2009-11-2 00:00:00', false),
 ('2009-11-2 01:00:00', false),
 . . .
 ('2009-11-2 08:00:00', true),
 . . .
 ('2009-11-2 15:00:00', true),
 ('2009-11-2 16:00:00', false),
 . . .
 ('2009-11-2 23:00:00', false);

Аналогичным образом добавьте 24 строки для каждого другого дня. Неважно, какой год или месяц вы дадите, как вы увидите через мгновение. Вам просто нужно представить все семь дней недели.

SELECT t.id, t.start, t.end, SUM(CASE WHEN h.is_working THEN 1 ELSE 0 END) AS hours_worked
FROM mytable t JOIN hours h 
ON (EXTRACT(DOW FROM TIMESTAMP h.hour) BETWEEN EXTRACT(DOW FROM TIMESTAMP t.start) 
      AND EXTRACT(DOW FROM TIMESTAMP t.end))
  AND (EXTRACT(DOW FROM TIMESTAMP h.hour) > EXTRACT(DOW FROM TIMESTAMP t.start)
      OR EXTRACT(HOUR FROM TIMESTAMP h.hour) >= EXTRACT(HOUR FROM TIMESTAMP t.start))
  AND (EXTRACT(DOW FROM TIMESTAMP h.hour) < EXTRACT(DOW FROM TIMESTAMP t.end)
      OR EXTRACT(HOUR FROM TIMESTAMP h.hour) <= EXTRACT(HOUR FROM TIMESTAMP t.end))
GROUP BY t.id, t.start, t.end;

Ответ 3

Следующие функции возьмут вход для
рабочее время начала дня
рабочее время окончания дня
время начала
время окончания

-- helper function
CREATE OR REPLACE FUNCTION get_working_time_in_a_day(sdt TIMESTAMP, edt TIMESTAMP, swt TIME, ewt TIME) RETURNS INT AS
$$
DECLARE
  sd TIMESTAMP; ed TIMESTAMP; swdt TIMESTAMP; ewdt TIMESTAMP; seconds INT;
BEGIN
  swdt = sdt::DATE || ' ' || swt; -- work start datetime for a day
  ewdt = sdt::DATE || ' ' || ewt; -- work end datetime for a day

  IF (sdt < swdt AND edt <= swdt) -- case 1 and 2
  THEN
    seconds = 0;
  END IF;

  IF (sdt < swdt AND edt > swdt AND edt <= ewdt)        -- case 3 and 4
  THEN
    seconds = EXTRACT(EPOCH FROM (edt - swdt));
  END IF;

  IF (sdt < swdt AND edt > swdt AND edt > ewdt)         -- case 5
  THEN
    seconds = EXTRACT(EPOCH FROM (ewdt - swdt));
  END IF;

  IF (sdt = swdt AND edt > swdt AND edt <= ewdt)        -- case 6 and 7
  THEN
    seconds = EXTRACT(EPOCH FROM (edt - sdt));
  END IF;

  IF (sdt = swdt AND edt > ewdt)                        -- case 8
  THEN
    seconds = EXTRACT(EPOCH FROM (ewdt - sdt));
  END IF;

  IF (sdt > swdt AND edt <= ewdt)                       -- case 9 and 10
  THEN
    seconds = EXTRACT(EPOCH FROM (edt - sdt));
  END IF;

  IF (sdt > swdt AND sdt < ewdt AND edt > ewdt)         -- case 11
  THEN
    seconds = EXTRACT(EPOCH FROM (ewdt - sdt));
  END IF;

  IF (sdt >= ewdt AND edt > ewdt)                       -- case 12 and 13
  THEN
    seconds = 0;
  END IF;

  RETURN seconds;
END;
$$
LANGUAGE plpgsql;

-- Get work time difference
CREATE OR REPLACE FUNCTION get_working_time(sdt TIMESTAMP, edt TIMESTAMP, swt TIME, ewt TIME) RETURNS INT AS
$$
DECLARE
  seconds INT = 0;
  strst VARCHAR(9) = ' 00:00:00';
  stret VARCHAR(9) = ' 23:59:59';
  tend TIMESTAMP; tempEdt TIMESTAMP;
  x int;
BEGIN
  <<test>>
  WHILE sdt <= edt LOOP
  tend = sdt::DATE || stret; -- get the false end datetime for start time
  IF edt >= tend 
  THEN
    tempEdt = tend;
  ELSE
    tempEdt = edt;
  END IF;
  -- skip saturday and sunday
  x = EXTRACT(DOW FROM sdt);
  if (x > 0 AND x < 6)
  THEN
     seconds = seconds + get_working_time_in_a_day(sdt, tempEdt, swt, ewt); 
   ELSE
  --   RAISE NOTICE 'MISSED A DAY';
   END IF;

  sdt = (sdt + (INTERVAL '1 DAY'))::DATE || strst;
  END LOOP test;
  --RAISE NOTICE 'diff in minutes = %', (seconds / 60);
  RETURN seconds;
END;
$$
LANGUAGE plpgsql;

-- Table Definition
DROP TABLE IF EXISTS test_working_time;
CREATE TABLE test_working_time(
  pk SERIAL PRIMARY KEY,
  start_datetime TIMESTAMP, 
  end_datetime TIMESTAMP, 
  start_work_time TIME, 
  end_work_time TIME
);

-- Test data insertion
INSERT INTO test_working_time VALUES 
(1,  '2015-11-03 01:00:00', '2015-11-03 07:00:00', '08:00:00', '22:00:00'),
(2,  '2015-11-03 01:00:00', '2015-11-04 07:00:00', '08:00:00', '22:00:00'),
(3,  '2015-11-03 01:00:00', '2015-11-05 07:00:00', '08:00:00', '22:00:00'),
(4,  '2015-11-03 01:00:00', '2015-11-06 07:00:00', '08:00:00', '22:00:00'),
(5,  '2015-11-03 01:00:00', '2015-11-07 07:00:00', '08:00:00', '22:00:00'),
(6,  '2015-11-03 01:00:00', '2015-11-03 08:00:00', '08:00:00', '22:00:00'),
(7,  '2015-11-03 01:00:00', '2015-11-04 08:00:00', '08:00:00', '22:00:00'),
(8,  '2015-11-03 01:00:00', '2015-11-05 08:00:00', '08:00:00', '22:00:00'),
(9,  '2015-11-03 01:00:00', '2015-11-06 08:00:00', '08:00:00', '22:00:00'),
(10, '2015-11-03 01:00:00', '2015-11-07 08:00:00', '08:00:00', '22:00:00'),
(11, '2015-11-03 01:00:00', '2015-11-03 11:00:00', '08:00:00', '22:00:00'),
(12, '2015-11-03 01:00:00', '2015-11-04 11:00:00', '08:00:00', '22:00:00'),
(13, '2015-11-03 01:00:00', '2015-11-05 11:00:00', '08:00:00', '22:00:00'),
(14, '2015-11-03 01:00:00', '2015-11-06 11:00:00', '08:00:00', '22:00:00'),
(15, '2015-11-03 01:00:00', '2015-11-07 11:00:00', '08:00:00', '22:00:00'),
(16, '2015-11-03 01:00:00', '2015-11-03 22:00:00', '08:00:00', '22:00:00'),
(17, '2015-11-03 01:00:00', '2015-11-04 22:00:00', '08:00:00', '22:00:00'),
(18, '2015-11-03 01:00:00', '2015-11-05 22:00:00', '08:00:00', '22:00:00'),
(19, '2015-11-03 01:00:00', '2015-11-06 22:00:00', '08:00:00', '22:00:00'),
(20, '2015-11-03 01:00:00', '2015-11-07 22:00:00', '08:00:00', '22:00:00'),
(21, '2015-11-03 01:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'),
(22, '2015-11-03 01:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'),
(23, '2015-11-03 01:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'),
(24, '2015-11-03 01:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'),
(25, '2015-11-03 01:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'),
(26, '2015-11-03 08:00:00', '2015-11-03 11:00:00', '08:00:00', '22:00:00'),
(27, '2015-11-03 08:00:00', '2015-11-04 11:00:00', '08:00:00', '22:00:00'),
(28, '2015-11-03 08:00:00', '2015-11-05 11:00:00', '08:00:00', '22:00:00'),
(29, '2015-11-03 08:00:00', '2015-11-06 11:00:00', '08:00:00', '22:00:00'),
(30, '2015-11-03 08:00:00', '2015-11-07 11:00:00', '08:00:00', '22:00:00'),
(31, '2015-11-03 08:00:00', '2015-11-03 22:00:00', '08:00:00', '22:00:00'),
(32, '2015-11-03 08:00:00', '2015-11-04 22:00:00', '08:00:00', '22:00:00'),
(33, '2015-11-03 08:00:00', '2015-11-05 22:00:00', '08:00:00', '22:00:00'),
(34, '2015-11-03 08:00:00', '2015-11-06 22:00:00', '08:00:00', '22:00:00'),
(35, '2015-11-03 08:00:00', '2015-11-07 22:00:00', '08:00:00', '22:00:00'),
(36, '2015-11-03 08:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'),
(37, '2015-11-03 08:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'),
(38, '2015-11-03 08:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'),
(39, '2015-11-03 08:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'),
(40, '2015-11-03 08:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'),
(41, '2015-11-03 12:00:00', '2015-11-03 18:00:00', '08:00:00', '22:00:00'),
(42, '2015-11-03 12:00:00', '2015-11-04 18:00:00', '08:00:00', '22:00:00'),
(43, '2015-11-03 12:00:00', '2015-11-05 18:00:00', '08:00:00', '22:00:00'),
(44, '2015-11-03 12:00:00', '2015-11-06 18:00:00', '08:00:00', '22:00:00'),
(45, '2015-11-03 12:00:00', '2015-11-07 18:00:00', '08:00:00', '22:00:00'),
(46, '2015-11-03 12:00:00', '2015-11-03 22:00:00', '08:00:00', '22:00:00'),
(47, '2015-11-03 12:00:00', '2015-11-04 22:00:00', '08:00:00', '22:00:00'),
(48, '2015-11-03 12:00:00', '2015-11-05 22:00:00', '08:00:00', '22:00:00'),
(49, '2015-11-03 12:00:00', '2015-11-06 22:00:00', '08:00:00', '22:00:00'),
(50, '2015-11-03 12:00:00', '2015-11-07 22:00:00', '08:00:00', '22:00:00'),
(51, '2015-11-03 12:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'),
(52, '2015-11-03 12:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'),
(53, '2015-11-03 12:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'),
(54, '2015-11-03 12:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'),
(55, '2015-11-03 12:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'),
(56, '2015-11-03 22:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'),
(57, '2015-11-03 22:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'),
(58, '2015-11-03 22:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'),
(59, '2015-11-03 22:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'),
(60, '2015-11-03 22:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'),
(61, '2015-11-03 22:30:00', '2015-11-03 23:30:00', '08:00:00', '22:00:00'),
(62, '2015-11-03 22:30:00', '2015-11-04 23:30:00', '08:00:00', '22:00:00'),
(63, '2015-11-03 22:30:00', '2015-11-05 23:30:00', '08:00:00', '22:00:00'),
(64, '2015-11-03 22:30:00', '2015-11-06 23:30:00', '08:00:00', '22:00:00'),
(65, '2015-11-03 22:30:00', '2015-11-07 23:30:00', '08:00:00', '22:00:00');

-- select query to get work time difference
SELECT 
  start_datetime,
  end_datetime,
  start_work_time,
  end_work_time,
  get_working_time(start_datetime, end_datetime, start_work_time, end_work_time) AS diff_in_minutes 
FROM
    test_working_time;

Это даст разницу только в рабочих часах в секундах между началом и концом datetime

Ответ 1

Закругленные результаты

Только для двух заданных временных меток

Для всей таблицы

Более высокая точность

Очиститель с LATERAL в Postgres 9.3 +

Точные результаты

Postgres 8.4 +

Postgres 9.2+ с tsrange

Ответ 2

Ответ 3

Очиститель с `LATERAL` в Postgres 9.3 +

Postgres 9.2+ с `tsrange`