MySql datetime 반올림

회사에서 동료분이 MySql datetime 처리 시 소수점 시간이 반올림된다는 내용을 공유해주셨습니다.

그래서 공식문서를 보며 테이블을 만들고 테스트한 내용을 기록하고자 글을 작성합니다.

 

공식문서를 보면 Mysql은 6자리의 소수 초(microSecond)를 time, datetime,timestamp 데이터 타입 사용시 지원한다고 명시되어있습니다. 아래 작성 부터는 소수초를 ms라고 칭하겠습니다.

ms 설정은 0~6 범위 내의 값이어야하고, 0은 ms 없음을 의미하고, 생략하면 ms는 0입니다.

아래 쿼리에서 date_start는 ms 사용 X, date_end는 ms 6자리를 사용하도록 작성했습니다. 

CREATE TABLE `tb_test` (
	`date_start` DATETIME NOT NULL,
	`date_end` DATETIME(6) NOT NULL
)
COLLATE='utf8_general_ci';

INSERT INTO tb_test(date_start, date_end) VALUES('2023-08-31 00:00:00.543', '2023-08-31 00:59:59.4325')

INSERT INTO tb_test(date_start, date_end) VALUES('2023-08-31 00:00:00.543', '2023-08-31 00:59:59.4325565')

date_start를 보면 ms가 반올림되어 처리된 것을, date_end를 보면 6자리 고정으로 ms가 등록된 것을 확인 할 수 있습니다.

date_end에 6자리를 넘는 ms등록 시 6자리를 넘는 값은 반올림 처리가 됩니다.

두번 째 데이터를 쿼리, 데이터로 확인할 수 있습니다.

NOW()함수 사용 시에도 ms 사용이 가능하다.

5.x버전에서 반올림이 발생하면 경고나 오류가 제공되지 않고, SQL 표준을 따르며 서버 sql_mode 설정의 영향을 받지 않습니다.

5.7버전에서 sql_mode를 설정하려고 했을 때 해당 시스템 변수를 지원하지 않아 아래와 같이 값을 설정할 수 없다는 에러가 뜨는 것을 확인할 수 있었습니다.

8.1문서를 확인해보면, 반올림 옵션을 비활성 할 수 있는 sql_mode를 제공합니다.

TIME_TRUNCATE_FRACTIONAL 옵션은 time, datetime, timestamp 값 을 삽입할 때 반올림 또는 버림이 발생하는지 제어합니다. 이 모드가 활성화되면 버림이 발생합니다. 

 

아래 예시는 MySql문서에 작성되어 있습니다. (8.0 이상)

1번 데이터는 반올림 될 것을 예상할 수 있고 2번 데이터는 해당 모드를 활성했기 때문에 버림 된 것을 예상할 수 있습니다.

CREATE TABLE t (id INT, tval TIME(1));
SET sql_mode='';
INSERT INTO t (id, tval) VALUES(1, 1.55);
SET sql_mode='TIME_TRUNCATE_FRACTIONAL';
INSERT INTO t (id, tval) VALUES(2, 1.55);

결과 테이블 내용은 다음과 같습니다. 여기서 첫 번째 값은 반올림되고 두 번째 값은 잘립니다.

mysql> SELECT id, tval FROM t ORDER BY id;
+------+------------+
| id   | tval       |
+------+------------+
|    1 | 00:00:01.6 |
|    2 | 00:00:01.5 |
+------+------------+

 

참고 : https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

https://dev.mysql.com/doc/refman/8.1/en/fractional-seconds.html

https://dev.mysql.com/doc/refman/8.1/en/sql-mode.html#sqlmode_time_truncate_fractional