[SQL 쿡북] 8. 날짜 산술

기본적인 날짜 산술에 중점을 두고 설명합니다. 다음 장에서는 더 많은 고급 날짜 레시피를 찾아볼 수 있습니다. 

 

8.1 일, 월, 연도 가감하기

8.2 두 날짜 사이의 일수 알아내기

MySQL의 DATEDIFF에는 두 개의 매개변수(차이를 찾을 두 날짜)만 필요하며, 음숫값을 피하고자 두 날짜 중 더 작은 날짜를 먼저 전달해야 합니다.(SQL Server와 반대).

 

8.3 두 날짜 사이의 영업일수 알아내기

휴일도 제외하려면 HOLIDAYS 테이블을 만들어서 처리합니다. 해법에서 간단한 NOT IN 술어를 추가하여 HOLIDAYS에 나열된 날짜를 제외합니다. 

 select max(case when ename = 'BLAKE'
                  then hiredate
            end) as blake_hd,
        max(case when ename = 'JONES'
                 then hiredate
             end) as jones_hd
   from emp
  where ename in ( 'BLAKE','JONES' )

 select sum(case when date_format(
                          date_add(jones_hd,
                                   interval t500.id-1 DAY),'%a')
                    in ( 'Sat','Sun' )
                  then 0 else 1
             end) as days
    from (
  select max(case when ename = 'BLAKE'
                  then hiredate
            end) as blake_hd,
        max(case when ename = 'JONES'
                 then hiredate
             end) as jones_hd
   from emp
  where ename in ( 'BLAKE','JONES' )
        ) x,
        t500
  where t500.id <= datediff(blake_hd,jones_hd)+1

1. 시작 날짜와 종료 날짜 사이의 날짜(포함)을 반환합니다.

2. 주말을 제외하고 며칠(행)이 있는지 계산합니다. 

 

WHERE 절을 살펴보면 BLAKE_HD와 JONES_HD의 차이에 1을 더하여 필요한 30개 행을 생성함을 알 수 있습니다.(그렇지 않으면 29개 행이 생성됩니다.) 또한 ID 값이 1에서 시작하고 JONES_HD에 1을 더하면 JONES_HD가 최종 개수에서 제외될 수 있으므로, 외부 쿼리의 SELECT 목록에 있는 T500.ID에서 1을 빼는 것을 확인 할 수 있습니다.

결과셋에 필요한 행 수를 생성한 후에는 CASE 표현식을 사용하여 반환된 각 요일이 평일인지 주말인지 여부를 표시(평일에는 1을, 주말에는 0을 반환) 마지막 단계는 집계함수 SUM을 사용하여 1인 수를 집계하여 최종 답을 도출합니다.

 

8.4 두 날짜 사이의 월 또는 년 수 알아내기

1년은 항상 12개월이므로 두 날자 사이의 개월 수를 찾은 다음 12로 나누어 연도를 구할 수 있습니다. 해법에 익숙해지면 원하는 연도에 따라 결과를 반올림합니다. 예를 들어 EMP테이블의 첫 번째 HIREDATE는 12-DEC-1980이고 마지막은 12-JAN-1983입니다. 연도(1983-1908)에 대해 계산하면 3년이 되지만, 개월 차이는 (2년이 조금 넘는) 약 25개월입니다. 적합하다고 판단되는 쪽으로 조정해야 합니다. 다음 해법에서는 25개월 및 약 2년을 반환합니다.

 

YEAR 및 MONTH 함수를 사용하여 제공된 날짜에 대해 4자리 연도와 2자리 월을 반환합니다. 

<DB2와 MySQL>
 select mnth, mnth/12
   from (
 select (year(max_hd) - year(min_hd))*12 +
        (month(max_hd) - month(min_hd)) as mnth
   from (
 select min(hiredate) as min_hd, max(hiredate) as max_hd
   from emp
        ) x
        ) y

8.5 두 날짜 사이의 시,분,초 알아내기

 select datediff(allen_hd,ward_hd)*24 hr,
         datediff(allen_hd,ward_hd)*24*60 min,
         datediff(allen_hd,ward_hd)*24*60*60 sec
    from (
  select max(case when ename = 'WARD'
                   then hiredate
             end) as ward_hd,
         max(case when ename = 'ALLEN'
                  then hiredate
            end) as allen_hd
   from emp
        ) x

8.6 1년 중 평일 발생 횟수 계산하기

 

1. 연도에 가능한 모든 날짜를 생성합니다.

2. 해당 요일의 이름이 나오도록 날짜 형식을 지정합니다.

3. 각 요일의 발생 횟수를 셉니다.

<MySQL>
 select date_format(
            date_add(
                cast(
              concat(year(current_date),'-01-01')
                     as date),
                     interval t500.id-1 day),
                     '%W') day,
         count(*)
    from t500
  where t500.id <= datediff(
                          cast(
                        concat(year(current_date)+1,'-01-01')
                               as date),
                          cast(
                        concat(year(current_date),'-01-01')
                               as date))
 group by date_format(
             date_add(
                 cast(
               concat(year(current_date),'-01-01')
                      as date),
                      interval t500.id-1 day),
                      '%W')

테이블 T500에 대해 선택하여 해당 연도의 모든 일을 반환하기에 충분한 행을 생성합니다. DATE_FORMAT 함수를 사용하여 각 날짜의 요일명을 얻은 다음 요일별 발생 횟수를 계산합니다.

 

8.7 현재 레코드와 다음 레코드 간의 날짜 차이 알아내기

 select x.ename, x.hiredate, x.next_hd,
        datediff(x.next_hd, x.hiredate) as diff
   from (
 select deptno, ename, hiredate,
        lead(hiredate)over(order by hiredate) as next_hd
   from emp e
        ) x
  where e.deptno=10

윈도우 함수 LEAD를 사용한 다음, 8.2절에서 설명한 기술을 사용하여 두 날짜 간의 차이를 찾습니다. 

추가적인 조인 없이 현재 행 주변의 행에 액세스하여 더 읽기 쉽고 효율적인 코드를 제공합니다.

 

 

8.8 마치며

날짜는 일반적인 데이터 유형이지만, 단순한 숫자 데이터 유형보다 구조가 더 복잡한 만큼 독특합니다. 핵심 함수들을 마스터하면 날짜를 쉽게 처리할 수 있습니다.