在PostgreSQL中计算2个日期之间的工作时间

1tu0hz3e  于 2022-12-23  发布在  PostgreSQL
关注(0)|答案(3)|浏览(429)

我正在用Postgres（PL/pgSQL）开发一个算法，我需要计算两个时间戳之间的工作时间，考虑到周末不工作，其余的日子只计算从上午8点到下午15点。
示例：

  • 从12月3日下午14点到12月4日上午9点应计为2小时：
3rd = 1, 4th = 1
  • 从12月3日下午15点到12月7日上午8点应计为8小时：
3rd = 0, 4th = 8, 5th = 0, 6th = 0, 7th = 0

考虑小时分数也是很好的。

postgresql

3条答案

按热度按时间
ergxz8rk

ergxz8rk1#

根据您的问题，***工作时间***为：周一至周五，上午8时至15时。

舍入结果

仅针对两个给定的时间戳

以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()在结束时间大于开始时间的情况下生成一行，并在每个 * full * 给定间隔（1小时）生成另一行。此wold计数输入到**中的每个小时。要忽略小时的小数部分，请从结束时间减去1小时。并且不计数14：00之前开始的小时。
  • 对于EXTRACT()，请使用字段模式**ISODOW**而不是DOW来简化表达式。对于星期日，返回7而不是0。
  • 对time的简单（而且非常便宜）转换可以很容易地确定合格的小时数。
  • 小时的分数将被忽略，即使间隔开始和结束时的分数加起来等于或大于一小时。

对于整个表

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〈〉小提琴here *

老SQLF

更精准

为了获得更高的精确度，你可以使用更小的时间单位，例如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的更多信息：

  • 使用group by查找数组中最常见的元素
  • 根据一个表中的数字在另一个表中插入多行

精确结果

波茨格雷斯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〈〉小提琴here *

老SQLF

采用tsrange的Postgres 9.2 +

新的量程类型与intersection operator *相结合，为精确结果提供了更优雅的解决方案：
时间范围仅跨越一天的简单函数：

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

如果您的范围从不跨越多天，那么您只需要这么做。
否则，使用这个 Package 器函数来处理 * any * interval：

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〈〉小提琴here *

老SQLF

赞(0）分享  回复(0）举报  2022-12-23
w41d8nur

w41d8nur2#

不如这样：创建一个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）分享  回复(0）举报  2022-12-23
umuewwlo

umuewwlo3#

以下函数将获取
一天的工作开始时间
一天的工作结束时间
开始时间
结束时间

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

这将只给予开始日期时间和结束日期时间之间以秒为单位的工作时间差

赞(0）分享  回复(0）举报  2022-12-23
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com