在PostgreSQL中计算两个日期之间的工作时间 [英] Calculate working hours between 2 dates in PostgreSQL

查看：340 发布时间：2020/5/29 19:44:09 sql postgresql date-arithmetic generate-series range-types

本文介绍了在PostgreSQL中计算两个日期之间的工作时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在开发一种使用Postgres(PL/pgSQL)的算法，我需要计算两个时间戳之间的工作时间，并考虑到周末不工作，而其余时间仅从上午8点到下午15点进行计数

I am developing an algorithm with Postgres (PL/pgSQL) and I need to calculate the number of working hours between 2 timestamps, taking into account that weekends are not working and the rest of the days are counted only from 8am to 15pm.

示例:

从12月3日下午14点到12月4日上午9点应该算上2个小时:

From Dec 3rd at 14pm to Dec 4th at 9am should count 2 hours:

3rd = 1, 4th = 1

从12月3日下午15点到12月7日上午8点应该算上8个小时:

From Dec 3rd at 15pm to Dec 7th at 8am should count 8 hours:

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

最好考虑小时分数.

推荐答案

根据您的问题 工作时间 : Mo–Fr，08 :00–15:00 .

According to your question working hours are: Mo–Fr, 08:00–15:00.

以 1小时为单位运行.分数被忽略，因此不是 precise 而是简单的:

Operating on units of 1 hour. Fractions are ignored, therefore not precise but simple:

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小时)内，每个 full 都会生成另一行.每小时输入的次数.要忽略小数小时，请从末尾减去1小时.而且不要计算从14:00开始的小时数.

The function generate_series() generates one row if the end is greater than the start and another row for every full given interval (1 hour). This wold count every hour entered into. To ignore fractional hours, subtract 1 hour from the end. And don't count hours starting before 14:00.

使用字段模式 ISODOW 代替DOW来表示

Use the field pattern ISODOW instead of DOW for EXTRACT() to simplify expressions. Returns 7 instead of 0 for Sundays.

对time进行简单(且非常便宜)的转换可以轻松确定排位赛时间.

A simple (and very cheap) cast to time makes it easy to identify qualifying hours.

一个小时的分数将被忽略，即使该间隔开始和结尾的分数加起来等于一个小时或更多.

Fractions of an hour are ignored, even if fractions at begin and end of the interval would add up to an hour or more.

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

查询:

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小提琴.

要获得更高的精度，可以使用较小的时间单位.例如5分钟的片:

To get more precision you can use smaller time units. 5-minute slices for instance:

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;

单位越小，成本越高..

The smaller the unit the higher the cost.

与新的 LATERAL 功能结合使用对于Postgres 9.3，上面的查询可以这样写:

In combination with the new LATERAL feature in Postgres 9.3, the above query can then be written as:

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;

这具有附加优势，与上述版本一样，结果中也不排除包含零工作时间的间隔.

This has the additional advantage that intervals containing zero working hours are not excluded from the result like in the above versions.

有关LATERAL的更多信息:

Find most common elements in array with a group by
Insert multiple rows in one table based on number in another table

或者您分别处理时间范围的开始和结束，以使 exact 的结果精确到微秒.使查询更复杂，但更便宜且更精确:

Or you deal with start and end of the time frame separately to get exact results to the microsecond. Makes the query more complex, but cheaper and exact:

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小提琴.

新的范围类型结合简单的功能，适用于仅跨越一天的时间范围:

Simple function for time ranges spanning only one day:

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;

如果您的范围从未跨越多天，那么这就是您所需要的.
否则，使用此包装器函数来处理 any 间隔:

If your ranges never span multiple days, that's all you need.
Else, use this wrapper function to deal with any interval:

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小提琴.

这篇关于在PostgreSQL中计算两个日期之间的工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

在PostgreSQL中计算两个日期之间的工作时间 [英] Calculate working hours between 2 dates in PostgreSQL

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

在PostgreSQL中计算两个日期之间的工作时间 [英] Calculate working hours between 2 dates in PostgreSQL

问题描述

推荐答案

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭