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

我正在使用Postgres(PL / pgsql)开发一个算法,我需要计算2个时间戳之间的工作时数,考虑到周末不工作,其余时间只能从上午8点到下午15点进行. @H_502_1@例子：

@H_502_1@>从12月3日14时至12月4日上午9时应计2小时：

3rd = 1,4th = 1

@H_502_1@>从12月3日下午15点到12月7日上午8点应该算8小时：

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

@H_502_1@考虑小时数也是很好的.

根据你的问题,工作时间是：Mo-Fr,08：00-15：00. @H_502_1@圆圆结果

@H_502_1@只有两个给定的时间戳

@H_502_1@以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';

@H_502_1@>如果结束大于开始,则每个完整给定间隔(1小时)的函数 generate_series()生成一行.这个数字每小时输入一次.忽略分数小时,从最后减去1小时.而不要在14:00之前开始计时.
>使用字段模式ISODOW而不是DOW for EXTRACT()来简化表达式.星期日返回7而不是0.
>一个简单的(非常便宜的)时间使得很容易识别符合条件的时间.
>忽略一小时的分数,即使间隔开始和结束的分数加起来一个小时或更长时间.

@H_502_1@整个桌子

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

@H_502_1@查询：

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;

@H_502_1@SQL Fiddle.

@H_502_1@更精准

@H_502_1@为了获得更高的精度,您可以使用较小的时间单位.例如：5分钟的切片

SELECT t_id,count(*) * interval '5 min' AS work_interval
FROM  (
   SELECT t_id,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;

@H_502_1@单位越小成本越高.

@H_502_1@在Postgres中使用LATERAL清洁9.3

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

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

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

@H_502_1@这另外的优点是,如上述版本所示,不会从结果中排除包含零工作时间的间隔.

@H_502_1@更多关于LATERAL：

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

@H_502_1@确切的结果

@H_502_1@Postgres 8.4

@H_502_1@或者您分别处理时间段的开始和结束,以获得精确的结果到微秒.使查询更复杂,但更便宜和更精确：

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;

@H_502_1@SQL Fiddle.

@H_502_1@Postgres 9.2与tsrange

@H_502_1@新系列类型提供了更加优雅的解决方案,结合intersection operator *的精确结果：

@H_502_1@时间范围仅限一天的简单功能：

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;

@H_502_1@如果您的范围从不跨越多天,那就是您需要的.
否则,使用这个包装函数来处理任何间隔：

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

@H_502_1@呼叫：

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

@H_502_1@SQL Fiddle.

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

猜你在找的Postgre SQL相关文章