mysql의 datediff에 대해서 알아봅시다. 물론, 다른 SQL에서는 1번째 인자로 day의 차이를 구할 것인지, year의 차이를 구할 것인지, 등등을 받습니다. 그러면 몇 일 차이나는지, 몇 달이 차이나는지 등을 구할 수 있을 거에요. 그런데 mySQL에서는 그렇지 않습니다. 2개의 인자만 있는데요. date 2개를 받아서 그 차이를 리턴해 줍니다.

 

 

DATEDIFF(t1,t2);

 

 

 t1과 t2의 차이를 리턴해 줍니다. t1이 더 이전의 날짜라면, 0, 또는 음수 값이 리턴됩니다.

 


 예제를 몇 개 보고, 실제 sakila 데이터 베이스에서 어떻게 적용할 수 있는지 보도록 하겠습니다.

 

 

 먼저 아래 쿼리를 작성해 보겠습니다. 2010/8/25와 2011/8/25의 차이를 알고 싶어요. 그런데, 2011년은 윤년이 아니니까, 차이가 365가 될 겁니다. 그런데 1번째 인자와 2번째 인자 중에서, 날짜 상으로 앞선 게 1번째인가요? 따라서 음수가 리턴이 될 거에요.

 

 

 -365가 리턴되었다는 것을 알 수 있어요.

 

 

 그러면 거꾸로 이 경우에는 어떨까요? 앞에 인자가 뒤에 인자보다 날짜상으로 뒤에 있어요. 2011년 8월 25일은 2010년 8월 25일보다 1년 뒤에 있어요. 따라서, 0보다 크거나 같은 값이 리턴됩니다.

 

 

 365가 리턴이 됩니다.

 

 

 그러면 이 경우에는 어떨까요? 2011년 8월 25일 3시 4분 5초에서, 2010년 8월 24일 23시 23분 23초를 빼면 어떤 결과가 나올까요? 결국은, 두 date의 차이를 리턴하는 것은 맞아요. 그런데, numbers of day를 돌려줍니다. 날짜의 수입니다. 그러면, 뒤에 붙어 있는 hh:mm:ss는 무시가 될 텐데요.

 

 

 2011/8/25에서 2010/8/24를 뺀 값은 366입니다. 따라서 366이 리턴됩니다.

 


 sakila 데이터 베이스에서, rent한지 60일이 지난 주문 번호만 출력해 봅시다. return한 기록이 없으면, last_update에서 rental 날짜를 뺍니다. 이것을, timediff로 적절히 표현하지는 못합니다. 이것은 838:59:59밖에 표현을 하지 못하기 때문입니다. datediff는 60일 차이 정도는 정확하게 표현할 수 있어요.

 

 

 그러면 이렇게 쿼리를 작성하면 될 거에요. where 절만 간단하게 봅시다.

 

 

 datediff의 1번째 인자에, ifnull이 들어가 있는데요. 이것은, return_date가 null값일 때, last_update를 1번째 인자로 취하겠다는 거에요. 만약에, null이 아니라면, return_date가 들어갈 거에요. 주문번호 14098을 보면..

 

 

 돌려준 날짜가 NULL임을 알 수 있어요. 따라서, 데이터가 마지막으로 업데이트가 된 날짜인 2006-2-23 04:12:08이, borrow date를 계산하기 위한 기준 값이 된 것입니다. 2005-8-21로부터는 대략 4달 차이이니까, 60일이 넘은 것은 맞습니다. 그러면 borrow 한 지, 60일 이상이 지난 주문이 있는, 고객 이름은 어떻게 출력을 해야 할까요?

 

 

 그리 복잡하지 않아요. customer는 고객 정보가 있는 테이블인데요. rental table에도 customer_id가 있고, customer에도 고객 id가 있을 겁니다. 그러면 4번째 조건이 이해가 가실 거에요. 2번째 from 절에 의해서 rental하고 customer 테이블을 카티션 곱한 결과 중에서, customer_id가 같은 것만 필터링을 할 거에요. 그리고 필터 조건이 하나 더 있는데, 빌린 시간이 60일 이상 넘은 것에 대해서만, 결과값에 나올 거에요.

 

 first_name과 공백, 그리고 last_name을 concat 하면 이름이 출력됩니다. 

 

 실행 결과는 위와 같습니다.