mysql timediff 함수 : 두 시간의 차이를 계산한다.

 DATETIME, TIME의 차이를 계산할 수 있는 방법이 없을까요? mysql에는 timediff 함수가 있습니다. 이것은 2개의 datetime/time의 차이를 time 자료형으로 리턴해 주는 함수입니다. 당연하게도, 2개의 자료형은 같아야 합니다.

 

 

timediff(T1,T2);

 

 

 T1과 T2의 차이를 돌려줍니다. 만약에 T1보다 T2가 더 나중에 있는 시간이라면 -hh:mm:ss를 돌려줍니다.

 

---

 

 먼저 아래 쿼리를 봅시다.

 

 이것은 time형 2개를 받았습니다. T1이 2시 3분 4초, T2가 2시 3분 2초입니다. T1이 T2보다 나중에 있는 시간이므로, 결과 값에 -가 붙지 않습니다. T1이 T2보다 얼만큼 나중에 있나요? 2초만큼 나중에 있습니다. 따라서, 00:00:02가 출력이 됩니다.

 

 

 출력에 00:00:02가 나옵니다. 그러면 반대로 T1을 2:3:2, T2를 2:3:4로 줘 봅시다.

 

 

 이 때에는 timediff의 수행 결과가 어떻게 나올까요? 천천히 생각해 봅시다. 2시 3분 2초는, 2시 3분 4초보다 2초 앞에 있나요? 뒤에 있나요? 2초 앞에 있어요. 그러면 이것은 -2초만큼 뒤에 있다는 것과 같습니다. 조금 헷갈리시다면, 이걸 생각해 봅시다. 3에서 2만큼을 빼면 1이 됩니다. 그러면, 1은 3에서 얼마만큼 더해야 하나요? -2만큼을 더해야 합니다. 마찬가지라고 생각하시면 됩니다.

 

 2시 3분 2초는 2시 3분 4초에서 2초만큼 빠진 거에요. 즉, T1은 T2에서 2만큼 빼면 됩니다. 그러면 T2에서 얼마를 더해야 T1이 나오나요? -2초요. 따라서 -2초가 결과값으로 출력이 됩니다.

 

 

 어렵지 않네요. 상대적인 시간의 차이라고 생각하시면 되겠어요.

 

 

 이제 DATETIME끼리 빼 봅시다. 2018-05-02 2:3:4에서 2018-05-01 2:3:2를 빼 봅시다. 그러면 어떻게 나올까요? 2018년 5월 2일 2시 3분 4초는, 2018년 5월 1일 2시 3분 2초보다 몇 시간 몇 분 몇 초 뒤에 있는 것인가요? 하루하고도 2초 뒤에 있습니다. 하루는 24시간입니다. 따라서, 24시간 0분 2초 뒤에 있다는 결과가 출력이 될 거에요.

 

 

 결과는 24:00:02가 나옵니다.

 

 

---

 이제, T1과 T2의 차이가 꽤 큰 경우를 생각해 봅시다.

 

 

 T1은 2040년 5월 3일 2시 3분 4초이고, T2는 2039년 5월 1일 2시 3분 4초입니다. 1년하고도 2일만큼의 시간 차이만큼이 나올까요?

 

 

 아닙니다. 838:59:59가 나옵니다. 34일 하고도 23시간보다 큰 시간 간격을 제대로 표시하지 못합니다. 마찬가지로, t1에 2039-05-01 2:3:4, T2에 2040-05-03 2:3:4를 넣으면 -838:59:59가 나올 거에요. 이는 sql 838 키워드로도 찾아볼 수 있는 문제 중 하나입니다. 이것은 두 값의 차이가 time으로 리턴되어서 그렇습니다. 이를 해결하는 방법은 다음에 알아보도록 합시다.

 

 

---

 그러면 예제 하나를 봅시다.

 

 

 아마 MYSQL에서는 sakila에 rental 테이블이 있을 거에요. 이 테이블에는 rental_date와 return_date가 있어요. 이 둘은 모두 datetime형입니다. YYYY-MM-DD hh:mm:ss 꼴로 표현이 되어 있는데요.

 

 

 이 쿼리로 내용들을 모두 출력해 봅시다.

 

 

 대충 요래 나올 거에요. 여기서 우리는, last_update를 한 시점을 현재라고 가정했을 때, 어느 기간만큼 빌렸는지를 출력하고 싶습니다. 그 기간을 hh:mm:ss로 print를 하고 싶어요. 어떻게 하면 될까요?

 

 

 단순히 이렇게 하면 될까요? 안 됩니다. 이 때에는, timediff를 쓰시면 되긴 합니다. 단, 두 time의 차이가  839:00:00보다 작을 때에만 올바른 결과를 리턴합니다. 그거는 일단 무시하기로 하고요. 만약에 아직 리턴을 하지 않았다면, last_update한 시간에서 빌린 시간을 빼도록 합시다.

 

 아직 borrow를 했는데 return을 하지 않았다면, 돌려준 날짜가 NULL로 채워져 있습니다. 그러면 ifnull 함수를 써서 적절하게 처리해 주면 될 거에요.

 

 보시면 ifnull의 1번째 인자가 return_date임을 알 수 있는데요. 만약에 이 필드의 값이 NULL이라면 대신 last_update로 대치하고 있음을 알 수 있습니다.

 

 

 그러면 대다수의 경우는 정확한 값이 출력되는데요. id가 14098인 친구에 대해서는 838:59:59가 print가 됩니다.

 

 

 실제로 14098에 대해서 출력해 보니, last_update와 빌린 날짜의 차이가 대략 4달 정도 됩니다. 이는 34일보다는 확실히 큰 것이기 때문에, 838:59:59가 출력이 됩니다. 원래 오늘, limit offset 키워드에 대해 올리려고 하였습니다. 그런데, 솔직히 말해서, 이 부분 상당히 골치가 아픈 친구들이였습니다. 조금 더 공부하고 올리도록 하겠습니다.