[SQL 쿡북] 9.날짜 조작 기법

9장의 해법은 날짜뿐만 아니라 시간도 포함하는 더 복잡한 쿼리로 확장되어 향후 작업을 위한 중요한 토대를 형성합니다. 

 

9.1 연도의 윤년 여부 결정하기 

<Oracle>
 select to_char(
          last_day(add_months(trunc(sysdate,'y'),1)),
         'DD')
   from t1

LAST_DAY 함수를 사용하여 2월의 마지막 날을 찾습니다.

현재 날짜에서 해당 연도의 일수를 뺀 다음, 1일을 더하여 현재 연도의 첫 번째 날을 찾음.

9.2 연도의 날짜 수 알아내기

 

현재 연도의 일수는 다음 해의 첫날과 올해의 첫날(일)간 차이입니다. 

1. 올해의 첫 번째 날을 찾습니다.

2. 다음 해의 첫 번째 날을 알기 위해 1년을 해당 날짜에 추가합니다.

3. 2단계의 결과에서 현재 연도를 뺍니다.

 

<Oracle>
 select add_months(trunc(sysdate,'y'),12) - trunc(sysdate,'y')
   from dual

ADDDATE를 사용하면 현재 연도의 시작을 찾을 수 있습니다.

DATEDIFF 및 구간 연산을 사용하여 연도의 일수를 결정합니다. 

 

9.3 날짜에서 시간 단위 추출하기

9.3 날짜에서 시간 단위 추출하기
<DB2>
 select    hour( current_timestamp ) hr,
         minute( current_timestamp ) min,
         second( current_timestamp ) sec,
            day( current_timestamp ) dy,
          month( current_timestamp ) mth,
            year( current_timestamp ) yr
   from t1
--------------------------------------------------------------------
select
        extract(hour from current_timestamp)
      , extract(minute from current_timestamp
      , extract(second from current_timestamp)
      , extract(day from current_timestamp)
      , extract(month from current_timestamp)
      , extract(year from current_timestamp)

<Oracle>
  select to_number(to_char(sysdate,'hh24')) hour,
         to_number(to_char(sysdate,'mi')) min,
         to_number(to_char(sysdate,'ss')) sec,
         to_number(to_char(sysdate,'dd')) day,
         to_number(to_char(sysdate,'mm')) mth,
         to_number(to_char(sysdate,'yyyy')) year
   from dual

<PostgreSQL>
 select to_number(to_char(current_timestamp,'hh24'),'99') as hr,
        to_number(to_char(current_timestamp,'mi'),'99') as min,
        to_number(to_char(current_timestamp,'ss'),'99') as sec,
        to_number(to_char(current_timestamp,'dd'),'99') as day,
        to_number(to_char(current_timestamp,'mm'),'99') as mth,
        to_number(to_char(current_timestamp,'yyyy'),'9999') as yr
   from t1

<MySQL>
 select date_format(current_timestamp,'%k') hr,
        date_format(current_timestamp,'%i') min,
        date_format(current_timestamp,'%s') sec,
        date_format(current_timestamp,'%d') dy,
        date_format(current_timestamp,'%m') mon,
        date_format(current_timestamp,'%Y') yr
   from t1

<SQL Server>
 select datepart( hour, getdate()) hr,
        datepart( minute,getdate()) min,
        datepart( second,getdate()) sec,
        datepart( day, getdate()) dy,
        datepart( month, getdate()) mon,
        datepart( year, getdate()) yr
   from t1

9.4 월의 첫 번째 요일과 마지막 요일 알아내기

<Oracle>
 select trunc(sysdate,'mm') firstday,
        last_day(sysdate) lastday
   from dual

TRUNC 함수를 사용하여 월의 1일을 찾고 LAST_DAY 함수를 사용하여 해당 월의 마지막 날을 찾습니다.

<MySQL>
 select date_add(current_date,
                 interval -day(current_date)+1 day) firstday,
        last_day(current_date) lastday
   from t1

DATE_ADD 및 DAY 함수를 사용하여 현재 날짜가 있는 월의 일수를 찾습니다. 그런 다음 현재 날짜에서 해당 값을 빼고 1을 더하여 해당 월의 1일을 찾습니다. 이번 달의 마지막 날을 찾으려면 LAST_DAY 함수를 사용합니다.

 

9.5 연도의 특정 요일의 모든 날짜 알아내기

현재 연도의 모든 금요일을 찾으려면 현재 연도의 매일을 반환해야 합니다.

<Oracle>
   with x
     as (
 select trunc(sysdate,'y')+level-1 dy
   from t1
   connect by level <=
      add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')
 )
 select *
   from x
  where to_char( dy, 'dy') = 'fri'

먼저  TRUNC 함수를 사용하여 연도의 첫 번째 날을 찾습니다.

다음으로 CONNECT BY 절을 사용하여 현재 연도의 매일을 반환합니다.

마지막 단계는 TO_CHAR 함수를 사용하여 금요일만 남기는 것입니다.

<MySQL>
	  with recursive cal (dy,yr)
   as
     (
     select dy, extract(year from dy) as yr
   from
     (select adddate
             (adddate(current_date, interval - dayofyear(current_date)
   day), interval 1 day) as dy) as tmp1
   union all
     select date_add(dy, interval 1 day), yr
  from cal
  where extract(year from date_add(dy, interval 1 day)) = yr
  )
     select dy from cal
     where dayofweek(dy) = 6

 

9.6 월의 특정 요일의 첫 번째 및 마지막 발생일 알아내기

<Oracle>
select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday,
       next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday
  from dual

NEXT_DAY 및 LAST_DAY 함수를 약간의 날짜 산술과 함께 사용하여 현재 월의 첫 번째 및 마지막 월요일을 찾습니다.

 

<MySQL>
 select first_monday,
         case month(adddate(first_monday,28))
              when mth then adddate(first_monday,28)
                       else adddate(first_monday,21)
         end last_monday
   from (
  select case sign(dayofweek(dy)-2)
              when 0 then dy
              when -1 then adddate(dy,abs(dayofweek(dy)-2))
             when 1 then adddate(dy,(7-(dayofweek(dy)-2)))
        end first_monday,
        mth
   from (
 select adddate(adddate(current_date,-day(current_date)),1) dy,
        month(current_date) mth
   from t1
        ) x
        ) y

<<어려움 한번 더 살펴보고 정리하기>>

9.7 달력 만들기 p351

<Oracle>
  with x
     as (
  select *
    from (
  select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,
         to_char(trunc(sysdate,'mm')+level-1,'dd') dm,
         to_number(to_char(trunc(sysdate,'mm')+level-1,'d')) dw,
         to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,
         to_char(sysdate,'mm') mth
   from dual
  connect by level <= 31
        )
  where curr_mth = mth
 )
 select max(case dw when 2 then dm end) Mo,
        max(case dw when 3 then dm end) Tu,
        max(case dw when 4 then dm end) We,
        max(case dw when 5 then dm end) Th,
        max(case dw when 6 then dm end) Fr,
        max(case dw when 7 then dm end) Sa,
        max(case dw when 1 then dm end) Su
   from x
  group by wk
  order by wk

<MySQL>
with recursive  x(dy,dm,mth,dw,wk)
      as (
  select dy,
         day(dy) dm,
         datepart(m,dy) mth,
         datepart(dw,dy) dw,
         case when datepart(dw,dy) = 1
              then datepart(ww,dy)-1
              else datepart(ww,dy)
        end wk
   from (
 select date_add(day,-day(getdate())+1,getdate()) dy
   from t1
        ) x
  union all
  select dateadd(d,1,dy), day(date_add(d,1,dy)), mth,
         datepart(dw,dateadd(d,1,dy)),
         case when datepart(dw,date_add(d,1,dy)) = 1
              then datepart(wk,date_add(d,1,dy))-1
              else datepart(wk,date_add(d,1,dy))
         end
    from x
   where datepart(m,date_add(d,1,dy)) = mth
 )
 select max(case dw when 2 then dm end) as Mo,
        max(case dw when 3 then dm end) as Tu,
        max(case dw when 4 then dm end) as We,
        max(case dw when 5 then dm end) as Th,
        max(case dw when 6 then dm end) as Fr,
        max(case dw when 7 then dm end) as Sa,
        max(case dw when 1 then dm end) as Su
   from x
  group by wk
  order by wk;

9.8 해당 연도의 분기 시작일 및 종료일 나열하기

<Oracle>
 select rownum qtr,
        add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,
        add_months(trunc(sysdate,'y'),rownum*3)-1 q_end
   from emp
  where rownum <= 4

<PostgreSQL, MySQL, SQL Server>
with x (dy,cnt)
   as (
select dateadd(d,-(datepart(dy,getdate())-1),getdate()),
       1
  from t1
 union all
select dateadd(m,3,dy), cnt+1
  from x
 where cnt+1 <= 4
)
select dy
  from x

9.9 지정 분기의 시작일 및 종료일 알아내기

<Oracle>
select substr(yrq,1,4) yr, mod(yrq,10)*3 mth
  from (
select 20051 yrq from t1 union all
select 20052 yrq from t1 union all
select 20053 yrq from t1 union all
select 20054 yrq from t1
       ) x
----------------------------------------------------------------
select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end
  from (
select 20051 yrq from t1 union all
select 20052 yrq from t1 union all
select 20053 yrq from t1 union all
select 20054 yrq from t1
       ) x

1. 연도와 분기를 찾는다. 2. q_end 값은 분기 마지막 달의 첫째날이다 / LAST_DAY를 사용하면 마지막 

<MySQL>
select substr(cast(yrq as varchar),1,4) yr, mod(yrq,10)*3 mth
  from (
select 20051 yrq from t1 union all
select 20052 yrq from t1 union all
select 20053 yrq from t1 union all
select 20054 yrq from t1
       ) x
--------------------------------------------------------------------
select last_day(
    str_to_date(
         concat(
         substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end
  from (
select 20051 as yrq from t1 union all
select 20052 as yrq from t1 union all
select 20053 as yrq from t1 union all
select 20054 as yrq from t1
       ) x

원리는 위와 같다.


9.10 누락된 날짜 채우기

select distinct
       extract(year from hiredate) as year
  from emp

<Oracle>
   with x
      as (
  select add_months(start_date,level-1) start_date
    from (
  select min(trunc(hiredate,'y')) start_date,
         add_months(max(trunc(hiredate,'y')),12) end_date
    from emp
         )
   connect by level <= months_between(end_date,start_date)
 )
 select x.start_date MTH, count(e.hiredate) num_hired
   from x left join emp e
     on (x.start_date = trunc(e.hiredate,'mm'))
  group by x.start_date
  order by 1

<MySQL>
with recursive x (start_date,end_date)
     as
    (
      select
          adddate(min(hiredate),
          -dayofyear(min(hiredate))+1)  start_date
          ,adddate(max(hiredate),
          -dayofyear(max(hiredate))+1)  end_date
          from emp
       union all
          select date_add(start_date,interval 1 month)
          , end_date
          from x
          where date_add(start_date, interval 1 month) < end_date
      )

select x.start_date mth, count(e.hiredate) num_hired
 from x left join emp e
   on (extract(year_month from start_date)
          =
       extract(year_month from e.hiredate))
group by x.start_date

 

 

9.11 특정 시간 단위 검색하기

<DB2와 MySQL>
 select ename
   from emp
 where monthname(hiredate) in ('February','December')
    or dayname(hiredate) = 'Tuesday'

<Oracle과 PostgreSQL>
 select ename
   from emp
 where rtrim(to_char(hiredate,'month')) in ('february','december')
    or rtrim(to_char(hiredate,'day')) = 'tuesday'

RDBMS에서 제공하는 날짜 형식 지정 함수를 알아 두면 이를 응용하여 연도, 분기, 연도 및 분기 조합, 월 및 연도 조합 등으로 쉽게 검색할 수 있습니다.

 

9.12 날짜의 특정 부분으로 레코드 비교하기

<Oracle과 PostgreSQL>
 select a.ename ||
        ' was hired on the same month and weekday as '||
        b.ename as msg
   from emp a, emp b
 where to_char(a.hiredate,'DMON') =
       to_char(b.hiredate,'DMON')
   and a.empno < b.empno
 order by a.ename

<MySQL>
 select concat(a.ename,
        ' was hired on the same month and weekday as ',
        b.ename) msg
   from emp a, emp b
  where date_format(a.hiredate,'%w%M') =
        date_format(b.hiredate,'%w%M')
    and a.empno < b.empno
 order by a.ename

같은 달과 요일에 고용된 사원이 누구인지 확인!

첫 번째 단계는 각 사원이 다른 사원의 HIREDATE에 액세스할 수 있도록 EMP에 셀프 조인하는 것

다음 단계는 RDBMS에서 제공하는 날짜 형식 지정 함수를 사용하여 HIREDATE의 요일과 월을 비교하고 일치하는 날짜만 남기는 것

마지막 단계는 단순히 결과셋을 연결하여 메시지를 구성하는 것

 

 

9.13 중복 날짜 범위 식별하기

DB2, PostgreSQL, Oracle
 select a.empno,a.ename,
        'project '||b.proj_id||
        ' overlaps project '||a.proj_id as msg
   from emp_project a,
        emp_project b
  where a.empno = b.empno
    and b.proj_start >= a.proj_start
    and b.proj_start <= a.proj_end
    and a.proj_id != b.proj_id

<MySQL>
 select a.empno,a.ename,
        concat('project ',b.proj_id,
         ' overlaps project ',a.proj_id) as msg
   from emp_project a,
        emp_project b
  where a.empno = b.empno
    and b.proj_start >= a.proj_start
    and b.proj_start <= a.proj_end
    and a.proj_id != b.proj_id

EMP_PROJECT를 셀프 조인합니다. 각 벤더별 문자열 결합이 다름.

 

9.14 마치며

날짜는 SQL에서 벤더 간 표준화가 덜 이루어진 영역 중 하나.

 

https://github.com/devcys22/SQLCookbook/blob/main/ch9%EB%82%A0%EC%A7%9C%20%EC%A1%B0%EC%9E%91%20%EA%B8%B0%EB%B2%95.sql

GitHub - devcys22/SQLCookbook: SQL쿡북을 학습합니다.