MySQL에서 두 날짜 사이의 년·월·일·시·분·초 기간을 계산하는 함수 만들기

데이터베이스 작업을 하다 보면 두 날짜(또는 날짜+시간) 사이의 정확한 기간을 년, 월, 일, 시간, 분, 초 단위로 구해야 하는 경우가 자주 있습니다. 이 글에서는 이러한 계산을 한 번에 처리해 주는 MySQL 사용자 정의 함수(UDF)를 만드는 방법을 소개합니다.

Duration 함수와 Label123 헬퍼 함수

아래 예제는 두 개의 함수로 구성됩니다. 핵심 역할을 하는 Duration 함수와, 값 뒤에 단위 라벨을 붙여주고 단수·복수형을 자동으로 처리해 주는 Label123 헬퍼 함수입니다.

mysql> DROP FUNCTION IF EXISTS Duration;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP FUNCTION IF EXISTS Label123;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER //
mysql> CREATE FUNCTION Duration( dtd1 datetime, dtd2 datetime ) RETURNS CHAR(128)
    -> BEGIN
    -> DECLARE yyr,mon,mmth,dy,ddy,hhr,m1,ssc,t1 BIGINT;
    -> DECLARE dtmp DATETIME;
    -> DECLARE t0 TIMESTAMP;
    -> SET yyr = TIMESTAMPDIFF(YEAR,dtd1,dtd2);
    -> SET mon = TIMESTAMPDIFF(MONTH,dtd1,dtd2);
    -> SET mmth = mon MOD 12;
    -> SET dtmp = ADDDATE(dtd1, interval mon MONTH);
    -> SET dy = TIMESTAMPDIFF(DAY,dtd1,dtd2);
    -> SET ddy = TIMESTAMPDIFF(DAY,dtmp,dtd2);
    -> SET t0 = TIMESTAMPADD(DAY,dy,dtd1);
    -> SET t1 = TIME_TO_SEC(TIMEDIFF(dtd2,t0));
    -> SET hhr = FLOOR(t1/3600);
    -> SET m1 = FLOOR(t1/60) - 60*hhr;
    -> SET ssc = t1 - 3600*hhr - 60*m1;
    -> RETURN CONCAT( Label123(yyr,'year'), Label123(mmth,'month'),
    ->     label123(ddy,'day'), Label123(hhr,'hour'),
    ->     Label123(m1,'min'), Label123(ssc,'sec')
    -> );
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION Label123( ival int, clabel char(16) ) RETURNS VARCHAR(24)
    -> RETURN Concat( ival, ' ', clabel, If(ival=1,' ','s ') ); //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

함수 동작 원리

각 단계가 어떻게 계산되는지 살펴보면 다음과 같습니다.

년(year): TIMESTAMPDIFF(YEAR, ...)로 전체 경과 연도를 구합니다.
월(month): 총 개월 수에서 12로 나눈 나머지(MOD 12)를 통해 남은 개월 수를 계산합니다.
일(day): 시작일에 경과 개월 수를 더한 날짜(ADDDATE)를 기준으로 남은 일수를 구합니다.
시간·분·초: 일 단위까지 차감한 후 남은 시간을 TIME_TO_SEC로 초 단위로 변환한 뒤, 3600과 60으로 나누어 시간, 분, 초를 각각 산출합니다.

Label123 함수는 값이 1이면 단수형('year'), 그 외에는 복수형('years')을 반환하도록 처리하여 결과를 사람이 읽기 좋은 형태로 만들어 줍니다.

사용 예제

함수 생성이 완료되면 아래와 같이 간단하게 호출할 수 있습니다.

mysql> Select Duration('2000-08-04 06:09:46', '2011-07-01 05:05:36')AS 'Duration';
+-----------------------------------------------------+
| Duration                                            |
+-----------------------------------------------------+
| 10 years 10 months 26 days 22 hours 55 mins 50 secs |
+-----------------------------------------------------+
1 row in set (0.00 sec)

2000년 8월 4일 06시 09분 46초부터 2011년 7월 1일 05시 05분 36초까지의 기간이 10년 10개월 26일 22시간 55분 50초로 정확하게 계산된 것을 확인할 수 있습니다. 이처럼 함수를 한 번 등록해 두면 회원 가입 후 경과 기간, 상품 사용 기간, 이벤트 잔여 시간 등 다양한 상황에서 손쉽게 재사용할 수 있습니다.