search:

MySQL TIME 컬럼에 대한 연산

05 Mar 2014

안내

본 문서는 블로그의 운영자인 본인이 Stackoverflow에 올린 답변을 정리한 글입니다.

Stackoverflow URL

http://stackoverflow.com/questions/20311876/error-in-mysql-time-query/20312078

SQLFiddle URL

http://www.sqlfiddle.com/#!2/967f8/1

질문

다음과 같은 데이터가 존재한다.

mysql> SELECT * FROM tbl;
+------+----------+
| id   | ex_time  |
+------+----------+
|    1 | 10:05:10 |
|    2 | 23:33:12 |
+------+----------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM tbl2;
+------+--------+
| id   | timing |
+------+--------+
|    1 |      5 |
|    2 |     50 |
+------+--------+
2 rows in set (0.00 sec)

다음과 같은 SQL을 실행하면 “Illegal mix of collations for operation ‘<’” 에러가 발생한다.

SELECT *
FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.id
WHERE ex_time > ('17:22:24' + INTERVAL tbl2.timing MINUTE);
 
ERROR 1271 (HY000): Illegal mix of collations for operation '>'

ex_time은 TIME형 자료이다. 원인과 해결책은 무엇인가?

답변

최초에 필자는 ‘17:22:24’가 문자열이기 때문에 발생한 오류라고 생각했었다. 별다른 테스트 없이 다음과 같은 SQL을 사용하라고 답변했었으나 질문자는 동일한 오류를 얻고 있다고 회신하였다.

SELECT *
FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.id
WHERE ex_time > ('17:22:24' + INTERVAL tbl2.timing MINUTE);

원인 파악 결과 INTERVAL은 DATETIME에 대한 연산이며 DATETIME 값을 return하기 때문에 발생한 오류였다. 즉, TIME 컬럼과 DATETIME 컬럼을 비교 연산하기 때문에 발생한 오류였다.

INTERVAL이 DATETIME에 대한 날짜 연산이라면 ADDTIME()은 TIME형에 대한 날짜 연산함수이다. 따라서 질문자는 ADDTIME()을 이용하여 다음과 같이 SQL문을 작성해야 한다.

SELECT *
FROM tbl INNER JOIN tbl2 ON tbl.id = tbl2.id
WHERE ex_time > ADDTIME('17:22:24', CONCAT('00:', tbl2.timing, ':00'));

ADDTIME()은 첫 번째 인자는 DATE 혹은 DATETIME이어야 한다. 두 번째 인자는 INTERVAL과 다르게 TIME 형식이어야 하므로 CONCAT()을 이용하여 앞 뒤로 ‘00’을 붙여서 분(MINUTE)만 더하는 것을 표현하였다.

Overflow 방지하기

ADDTIME()은 두 번째 인자가 TIME 형식이기를 기대한다. 즉, “HH:MM:SS” 같은 형식이어야 하며 MM과 SS는 00~59까지의 숫자만 허용한다. HH는 23을 넘어도 상관없다. 만약 MM과 SS의 값이 60 이상인 경우 TIME 형식이 아니기 때문에 ADDTIME()은 NULL을 반환한다.

mysql> SELECT NOW(), ADDTIME(NOW(), CONCAT('00:', 70, ':00')) AS added;
+---------------------+-------+
| NOW()               | added |
+---------------------+-------+
| 2014-02-01 15:35:38 | NULL  |
+---------------------+-------+
1 row in set, 1 warning (0.00 sec)
 
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '00:70:00' |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

이 경우 다음과 같이 Overflow를 방지할 수 있다. 다음은 현재 시각에 70분을 더하는 예이다.

mysql> SELECT NOW(), ADDTIME(NOW(), CONCAT(CAST((70 / 60) AS UNSIGNED),':', 70 % 60, ':00')) AS added;
+---------------------+---------------------+
| NOW()               | added               |
+---------------------+---------------------+
| 2014-02-01 15:37:20 | 2014-02-01 16:47:20 |
+---------------------+---------------------+
1 row in set (0.00 sec)

“MySQL” 카테고리의 추천 글