주말을 제외하고 PostgreSQL에서 두 날짜 사이의 근무 시간을 계산하는 방법은 무엇입니까?

PostgreSQL에서 두 날짜 사이의 근무 시간 계산

문제 설명

주말이 그렇지 않다는 점을 고려하여 두 타임스탬프 사이의 근무 시간을 계산하는 알고리즘을 설계하세요. 근무일과 평일은 오전 8시부터 3시까지로 계산됩니다. pm.

해결책

1단계: 일일 근무 시간 식별

근무 시간을 평일 오전 8시부터 오후 3시까지로 정의:

WITH WorkingHours AS (
  SELECT '08:00'::time AS start_time, '15:00'::time AS end_time
)

2단계: 매일 간격 계산

일련의 간격 만들기 주어진 날짜 범위 내의 매일:

SELECT t_id,
       generate_series(date_trunc('day', t_start), date_trunc('day', t_end), '1 day') AS day
FROM   t

3단계: 각 간격에 대한 근무 시간 계산

각 날짜의 근무 시간을 보간하고 범위 유형 tsrange를 사용하여 분수 시간을 계산합니다. :

SELECT t_id,
       SUM(CASE
         WHEN EXTRACT(ISODOW FROM day) < 6 THEN
           COALESCE(
             f_worktime(day::timestamp + WorkingHours.start_time, day::timestamp + WorkingHours.end_time),
             '0'
           )
         ELSE
           '0'
       END) AS work_time
FROM   WorkingHours
CROSS JOIN (
  SELECT t_id, day
  FROM   temp
) AS temp
GROUP  BY 1

4단계: 분수 계산 기능 시간

소수 시간을 계산하는 f_worktime 함수 정의:

CREATE FUNCTION f_worktime(_start timestamp, _end timestamp)
RETURNS interval
LANGUAGE sql AS
$func$
SELECT COALESCE(upper(tsrange(_start::timestamp, _end::timestamp)) - lower(tsrange(_start::timestamp, _end::timestamp)), '0')::interval;
$func$
IMMUTABLE;

예

WITH t AS (
  SELECT 0 AS t_id, '2023-05-01 09:00'::timestamp AS t_start, '2023-05-01 11:30'::timestamp AS t_end
UNION ALL
  SELECT 1, '2023-05-02 10:00'::timestamp, '2023-05-02 18:00'::timestamp
UNION ALL
  SELECT 2, '2023-05-03 09:15'::timestamp, '2023-05-04 10:45'::timestamp
)
SELECT t.t_id, extract(hour from work_time) AS working_hours
FROM   t
CROSS JOIN (
  SELECT t_id, SUM(CASE WHEN EXTRACT(ISODOW FROM day) < 6 THEN f_worktime(day::timestamp + WorkingHours.start_time, day::timestamp + WorkingHours.end_time) ELSE '0' END) AS work_time
  FROM   WorkingHours
  CROSS JOIN (
    SELECT t_id, day
    FROM   (
      SELECT t_id,
             generate_series(date_trunc('day', t_start), date_trunc('day', t_end), '1 day') AS day
      FROM   t
    ) AS temp
  ) AS temp
  GROUP  BY 1
) AS temp;

출력:

| t_id | working_hours |
|------|---------------|
|    0 |             3 |
|    1 |             8 |
|    2 |             3 |

위 내용은 주말을 제외하고 PostgreSQL에서 두 날짜 사이의 근무 시간을 계산하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!