PostgreSQL에서 두 날짜 사이의 근무 시간을 정확하게 계산하는 방법은 무엇입니까?

PostgreSQL에서 두 날짜 사이의 근무 시간을 계산하는 방법

도전

두 날짜 사이의 근무 시간을 계산하는 것은 복잡할 수 있으며, 특히 요소를 고려할 때 더욱 그렇습니다. 주말이나 특정 근무시간 등. 이번 게시물에서는 이 작업을 효율적으로 처리하기 위해 PostgreSQL을 사용하는 여러 가지 접근 방식을 살펴보겠습니다.

주말 및 근무 시간 고려

근무 시간이 월요일부터 금요일까지, 오전 8시에서 오전 8시 사이라고 가정하겠습니다. 오전 0시와 오후 3시. 이 정의를 사용하면 다음과 같이 두 타임스탬프 사이의 근무 시간을 계산할 수 있습니다.

1. 주말 무시: 날짜가 주말(토요일 또는 일요일)에 속하는지 확인하고 제외합니다.
2. 시간 자르기: 타임스탬프를 다음으로 변환합니다. 근무 시간 내에 날짜와 시간을 포함하세요. 예를 들어 "2023-03-08 19:30:00"은 "2023-03-08 15:00:00"이 됩니다.
3. 시간 계산: 잘린 종료 시간과 잘린 시작 시간. 그러면 총 근무 시간이 제공됩니다.

PostgreSQL 솔루션

반올림된 결과

반올림된 결과를 얻으려면 다음을 사용할 수 있습니다. PostgreSQL의 generate_series() 함수는 근무 시간 범위 내에서 일련의 1시간 간격을 생성합니다. 그런 다음 지정된 기간 내에 해당하는 적격 간격의 수를 계산합니다. 쿼리 예시는 다음과 같습니다.

SELECT count(*) AS work_hours
FROM   generate_series('2023-03-08 14:00', '2023-03-09 09:00' - interval '1 hour', interval '1 hour') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

더 정밀한

더 정확한 결과를 얻으려면 5분 증분과 같은 더 작은 시간 단위를 사용할 수 있습니다. 다음 쿼리는 5분 정밀도의 결과를 제공합니다.

SELECT count(*) * interval '5 min' AS work_interval
FROM   generate_series('2023-03-08 14:01', '2023-03-09 09:00' - interval '5 min', interval '5 min') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

정확한 결과

정확한 결과를 얻으려면 시작을 별도로 처리하여 보다 미묘한 접근 방식을 취할 수 있습니다. 그리고 시간의 끝. 다음은 마이크로초 단위로 정확한 간격 결과를 제공하는 쿼리입니다.

SELECT t_id
     , COALESCE(h.h, '0')
       - 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 (
   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;

접근 방식 비교

제시된 다양한 접근 방식은 다양한 수준의 정밀도와 성능을 제공합니다.

반올림된 결과: 이 방법은 구현이 간단하고 특히 입력 시간이 작업 경계에 가까울 때 합리적인 추정치를 제공합니다. 시간 범위.
더욱 정밀함: 이 접근 방식은 더 작은 시간 단위를 사용하여 더 나은 정확도를 제공합니다. 대부분의 시나리오에서 성능에 미치는 영향은 미미합니다.
정확한 결과: 이 방법은 더 복잡하며 추가 계산이 필요합니다. 가장 정확한 결과를 제공하지만 계산 비용이 더 많이 들 수 있습니다.

결론

적절한 접근 방식을 선택하는 것은 필요한 정밀도와 성능 제약 조건에 따라 달라집니다. 일반적인 용도의 경우 5분 단위로 "더 정밀한" 방법을 사용하면 정확성과 효율성 사이의 균형이 잘 맞습니다. 그러나 절대적인 정확성이 가장 중요한 경우에는 "정확한 결과" 접근 방식을 사용하여 마이크로초까지 정확한 간격을 제공할 수 있습니다.

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