logo标签列表

在PostgreSQL中计算两个日期之间的工作小时数。

sqlpostgresqldate-arithmeticgenerate-seriesrange-types
19
我正在使用Postgres (PL/pgSQL)开发算法，需要计算两个时间戳之间的工作小时数，考虑到周末不工作，其他日子只从上午8点到下午3点计算。
示例：
从12月3日14:00到12月4日9:00应计为2小时：
3rd = 1, 4th = 1

  • 从12月3日下午3点到12月7日上午8点应计算为8小时：

    • 3rd = 0, 4th = 8, 5th = 0, 6th = 0, 7th = 0

    考虑到小时的分数会更好。

    需要注意的是，“8点到15点”的工作日包含7个小时。 - Erwin Brandstetter
    3个回答
    39
    根据您的问题，工作时间为：周一至周五，08:00-15:00。

    四舍五入的结果

    仅适用于两个给定的时间戳

    以每小时为单位进行操作。小数部分将被忽略，因此不是精确的，但非常简单：

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

    • 函数generate_series()生成一行数据，如果结束时间大于开始时间，则生成一行；对于每个完整的给定间隔（1小时），生成另一行。这将计算输入的每个小时。要忽略小数小时，请从结束时间减去1小时。不要计算从14:00之前开始的小时。

    • 为了简化表达式，使用字段模式ISODOW而非DOW来进行EXTRACT()操作。对于星期日，返回7而非0。

    • 通过将其简单地强制转换为time，可以轻松识别符合条件的小时。

    • 忽略不到1小时的时间分数，即使间隔的开头和结尾的时间分数加起来超过1小时或更长。

    整个表格

    CREATE 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
;

    查询：

    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;

    db<>fiddle 这里
    旧版 sqlfiddle

    更精确

    为了获得更高的精度，您可以使用更小的时间单位。例如，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;

    单位越小，成本越高。

    在Postgres 9.3+中使用LATERAL的清洁工具

    结合Postgres 9.3中的新LATERAL功能，上述查询可以写成：

    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;

    db<>fiddle 在这里
    旧版 sqlfiddle

    使用tsrange的Postgres 9.2+

    新的范围类型结合交集运算符*提供了更加优雅的解决方案，用于精确结果：

    仅跨越一天的时间范围的简单函数：

    CREATE OR REPLACE FUNCTION f_worktime_1day(_start timestamp, _end timestamp)
  RETURNS interval
  LANGUAGE sql IMMUTABLE 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$;

    如果您的范围从未跨越多天，那就是您所需要的。
    否则，请使用此包装函数处理任何时间间隔：
    CREATE OR REPLACE FUNCTION f_worktime(_start timestamp
                                    , _end timestamp
                                    , OUT work_time interval)
  LANGUAGE plpgsql IMMUTABLE 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$;

    呼叫：

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

    db<>fiddle 在这里
    旧版sqlfiddle

    1
    我已经开发了一个版本，它使用generate_series()函数的方式与8.3兼容。如果您愿意，可以将其复制到此答案中。 - Mike Sherrill 'Cat Recall'
    2
    这个答案应该被接受。我的确想要在X个“工作时间”之后找到一个特定的日期。例如，如果今天是星期一，我的期望就是星期三。如果今天是星期五，我的期望就是“下周三”。我会看看能否调整这些函数来回答这种情况。 - Ketema
    6
    这样如何：创建一个小表格，包含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;
    0
    以下函数将接受以下输入：
    一天的工作开始时间
    一天的工作结束时间
    开始时间
    结束时间
    -- 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;

    这将返回开始时间和结束时间之间仅工作小时的秒数差异

    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接