[SQL 쿡북] Chapter 8 : 날짜 산술 (MYSQL)

8.1 일,월,연도 가감하기 

한 사람의 고용날짜를 이용하여 고용 전후 5일, 5개월, 5년 을 반환하여 총 6개의 날짜를 반환하려고 한다. 

추가하거나 뺄 단위를 지정하는 INTERVAL 키워드와 덧셈 뺄셈을 사용한다. 

select hiredate - interval 5 day as hd_minus_5D,
	   hiredate + interval 5 day as hd_plus_5D,
       hiredate - interval 5 month as hd_minus_5M,
       hiredate + interval 5 month as hd_plus_5M,
       hiredate - interval 5 year as hd_minus_5Y,
       hiredate + interval 5 year as hd_plus_5Y
	from emp
  where deptno = 10

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

두 날짜 간의 차이를 일 단위로 나타내려고 한다. 예를들어 두 사원의 고용 날짜 간의 차이를 구한다.  

MYSQL은 음숫값을 피하고자 더 작은날짜를 먼저 전달해야 한다. (SQL Server와 반대)

select TIMESTAMPDIFF(day, allen_hd, ward_hd)
	from (
select hiredate as ward_hd
	from emp
  where ename = 'WARD'
		 ) x,
         (
select hiredate as allen_hd
	from emp
  where ename = 'ALLEN'
         ) y

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

두 개의 날짜가 주어졌을 때, 두 날짜 자체를 포함하여 두 날짜 사이에 영업일이 며칠인지 확인하려고 한다. 예를들어 1/10과 1/11 이면 영업일은 2일이다. 단, 토요일이나 일요일이 아닌 모든 날로 영업일을 정의한다. 

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

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

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

프롬절 x에서 고용일 중 가장 큰 날짜와 작은 날짜를 구한다. 그 사이의 월은 간단히 말하면 max_hd의 년수와 min_hd의 년수를 빼줘서 12개월을 곱하고, 월 수의차이를 더하므로 총 기간 차이의 월수가 나오게 된다. 그래서 최종 mnth는 두 날짜 사이의 개월수, mnth/12는 약 2.0833년 으로 도출된다. 

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
    where e.deptno = 10
		) x

LEAD함수를 이용해서 다음 행에 접근할 수 있다. 그리고 행의 고용일과 다음 행의 고용일 간의 몇일이 있는지 알아낼 수 있다.

MySQL | LEAD, LAG 윈도우 함수