当前位置： 文江博客 话题详情

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

发布于 2024-08-13 11:23:50 字数 349 浏览 7 评论 0 原文

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

示例：

  • 从 12 月 3 日下午 14 点到 12 月 4 日上午 9 点应算 2 小时：

    <前><代码>第三个 = 1，第四个 = 1

  • 从12月3日下午15点到12月7日上午8点应算8小时：

    <前><代码>第 3 个 = 0、第 4 个 = 8、第 5 个 = 0、第 6 个 = 0、第 7 个 = 0

最好也考虑一下小时分数。

原文

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.

Examples:

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

    3rd = 1, 4th = 1

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

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

It would be great to consider hour fractions as well.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（3）

少跟Wǒ拽 2024-08-20 11:23:50

根据您的问题，工作时间为：周一至周五，08:00–15:00。

舍入结果

仅针对两个给定时间戳，

以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() 如果末尾大于开头，则生成一行，并为每个 生成另一行完整的给定间隔（1 小时）。这个世界计数每个进入的小时。要忽略小数小时，请从末尾减去 1 小时。并且不要计算 14:00 之前开始的时间。


  • 使用字段模式 ISODOW 而不是 DOW 来表示EXTRACT() 来简化表达式。周日返回 7 而不是 0。

  • 简单（而且非常便宜）转换为时间可以轻松识别符合条件的时间。

  • 一小时的小数部分将被忽略，即使间隔开始和结束时的小数部分加起来可达一小时或更长。

对于整个表

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<>fiddle 此处
旧sqlfiddle

更精确

要获得更精确，您可以可以使用更小的时间单位。以 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;

这有一个额外的优势与上述版本一样，包含零工作时间的间隔不会从结果中排除。

有关横向的更多信息：

精确结果

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_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<>fiddle 此处< /a>
旧的 sqlfiddle

Postgres 9.2+ 与 tsrange

新的范围类型为 <结合交集运算符精确结果 code>*：

适用于仅跨越一天的时间范围的简单函数：

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

如果您的范围从不跨越多天，这就是您所需要的。
否则，使用此包装函数来处理任何间隔：

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<>fiddle 此处
旧sqlfiddle

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

Rounded results

For just two given timestamps

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

  • 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.

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

  • 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.

For a whole table

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
;

Query:

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<>fiddle here
Old sqlfiddle

More precision

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.

Cleaner with LATERAL in Postgres 9.3+

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

1-hour precision:

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-minute precision:

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.

More about LATERAL:

Exact results

Postgres 8.4+

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;

db<>fiddle here
Old sqlfiddle

Postgres 9.2+ with tsrange

The new range types offer a more elegant solution for exact results in combination with the intersection operator *:

Simple function for time ranges spanning only one day:

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

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

Call:

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

db<>fiddle here
Old sqlfiddle

回复 原文
離人涙 2024-08-20 11:23:50

怎么样：创建一个 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 行。您给出的年份或月份并不重要，稍后您就会看到。您只需要代表一周中的所有 7 天。

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;

How about this: create a small table with 24*7 rows, one row for each hour in a week.

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

Likewise add 24 rows for each of the other days. It doesn't matter what year or month you give, as you'll see in a moment. You just need to represent all seven days of the week.

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;
回复 原文
兲鉂ぱ嘚淚 2024-08-20 11:23:50

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

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

这将仅给出开始日期时间和结束日期时间之间的工作时间（以秒为单位）的差异

This following functions will take the input for the
working start time of the day
working end time of the day
start time
end time 

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

This will give the difference of only the work hours in seconds between the start and end datetime

回复 原文
~没有更多了~

关于作者

泪是无色的血

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

胡图图

文章 0 评论 0

zt006

文章 0 评论 0

z祗昰~

文章 0 评论 0

冰葑

文章 0 评论 0

野の

文章 0 评论 0

天空

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文