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

计算PostgreSQL中两个日期之间的工作时间,第1张

概述我正在使用Postgres(PL / pgSQL)开发一个算法,我需要计算2个时间戳之间的工作时数,考虑到周末不工作,其余时间只能从上午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 = 我正在使用Postgres(PL / pgsql)开发一个算法,我需要计算2个时间戳之间的工作时数,考虑到周末不工作,其余时间只能从上午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

考虑小时数也是很好的.

根据你的问题,工作时间是:Mo-Fr,08:00-15:00.

圆圆结果

只有两个给定的时间戳

以1小时为单位运行.分数被忽略,因此不精确但简单:

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

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

整个桌子

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_hoursFROM  (   SELECT t_ID,generate_serIEs (t_start,t_end - interval '1h',interval '1h') AS h   FROM   t   ) subWHERE  EXTRACT(ISodoW FROM h) < 6AND    h::time >= '08:00'AND    h::time <= '14:00'GROUP  BY 1ORDER  BY 1;

SQL Fiddle.

更精准

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

SELECT t_ID,count(*) * interval '5 min' AS work_intervalFROM  (   SELECT t_ID,t_end - interval '5 min',interval '5 min') AS h   FROM   t   ) subWHERE  EXTRACT(ISodoW FROM h) < 6AND    h::time >= '08:00'AND    h::time <= '14:55'  -- 15.00 - interval '5 min'GROUP  BY 1ORDER  BY 1;

单位越小成本越高.

在Postgres中使用LAteraL清洁9.3

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

1小时精度:

SELECT t.t_ID,h.work_hoursFROM   tleft   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 TRUEORDER  BY 1;

5分钟精度:

SELECT t.t_ID,h.work_intervalFROM   tleft   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 TRUEORDER  BY 1;

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

更多关于LAteraL:

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

确切的结果

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_intervalFROM   t CROSS JOIN varleft   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

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

时间范围仅限一天的简单功能:

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;

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

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;

呼叫:

SELECT t_ID,f_worktime(t_start,t_end) AS worktimeFROM   tORDER  BY 1;

SQL Fiddle.

总结

以上是内存溢出为你收集整理的计算PostgreSQL中两个日期之间的工作时间全部内容,希望文章能够帮你解决计算PostgreSQL中两个日期之间的工作时间所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/1169287.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022-06-02
下一篇 2022-06-02

发表评论

登录后才能评论

评论列表(0条)

保存