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

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

问题描述

我正在用 Postgres (PL/pgSQL) 开发一个算法，我需要计算 2 个时间戳之间的工作小时数，考虑到周末不工作，其余的日子只从上午 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 小时为单位运行.分数被忽略，因此不是精确而是简单:

    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() 如果结束大于开始则生成一行，并在每个 full 给定间隔(1 小时)内生成另一行.这将计算进入的每一小时.要忽略小数小时，请从末尾减去 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.

        对 EXTRACT() 以简化表达式.星期日返回 7 而不是 0.

        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;

        单位越小，成本越高.

        结合新的LATERAL 特性，那么上面的查询可以写成:

        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:

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

        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;

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

        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中2个日期之间的工作时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

    查看全文
    相关文章
    其他开发最新文章
    热门教程
    热门工具
    登录 关闭
    扫码关注1秒登录
    发送“验证码”获取 | 15天全站免登陆