[MySQL] datetime save 시 주의해야 할 점 (feat. Fractional Second, digit)

회사 코드에서 셀프 리뷰하면서 실수를 알아차렸다.

다음은 셀프 리뷰하면서, 코멘트한 내용이다.

헐, 이제보니 OffsetDateTime 스펙이 ms가 아니라 ns였네요. OffsetDateTime 스펙에 ms는 없고, ns만 있었군요. @_@

nano의 범위는 0 ~ 999,999,999 라고 하네요.

1,000,000 ns = 1ms 다보니, 정확히 하려면 999,000,000ns 이 999ms가 되겠네요.

저희는 시간 필드를 datetime 타입으로 쓰고 있기 때문에 999ms는 반올림하여 처리됩니다.

다음 MySQL 스펙을 참고하면, digit을 넘는 것은 반올림처리한다고 되어있습니다. https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

applyEndAt = 2022-08-19'T'23:59:59.999+09:00 으로 요청이 들어온다면, DB에선 2022-08-20 00:00:00 으로 보이게 됩니다.(JST zone) 그 이유는 아래에 설명드리겠습니다.

위 값을 unix_timestamp로 변환하면 1660921200 이고, 결과는 다음과 같습니다.

@Test
fun test() {
    val offsetDateTime = OffsetDateTime.of(2022, 8, 19, 23, 59, 59, 999000000, OffsetDateTime.now().offset)
    println("offsetDateTime = $offsetDateTime") // offsetDateTime = 2022-08-19T23:59:59.999+09:00

    val millis = offsetDateTime.toInstant().toEpochMilli()
    println("millis = $millis") // millis = 1660921199999
}

1660921199999 -> 2022년 8월 19일 금요일 오후 11:59:59.999 GMT+09:00 입니다.

datetime은 6digit 까지만 허용할 수 있습니다. (참고 : https://dev.mysql.com/doc/refman/8.0/en/datetime.html) 따라서, 위의 예제로한다면 datetime 필드는 최대 2022-08-19 23:59:59.999999 까지 표현할 수 있습니다.

하지만, 저희는 DDL에 digit 표시를 하지 않고 다음과 같이 만들었어요.

...
    apply_end datetime not null,
...

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

docs에서는 digit을 명시하지 않으면 default = 0 이라고 합니다.

그렇기에 저희 DB를 보시면, 소숫점은 나오지 않고 초 까지만 표시가 되죠.

다시 돌아와서, 위의 test 예제 OffsetDateTime을 millis로 변환한 1660921199999 값을 변환하면 다음과 같습니다.

1660921199999 -> 2022년 8월 19일 금요일 오후 11:59:59.999 GMT+09:00

digit = 0 으로 선언된 datetime 필드이기 때문에 .999 를 표현할 수가 없게 됩니다. 따라서 이를 MySQL에서 반올림 처리를 해주는 것입니다.

결과적으로 데이터베이스에 위와 같이 23:59:59 가 아니라 다음날 00:00:00으로 저장되는 것입니다.

digit이 0이기 때문에, 999ms 까지 필요도 없습니다! 500ms만 되어도 반올림처리가 되기 때문에 똑같이 다음날 00:00:00 으로 저장이 돼요. 반대로, 499ms는 0.4초 반올림하면 0이니까 날은 넘어가지 않고, 23:59:59로 표현이 됩니다.

TMI 영역

select unix_timestamp('2022-08-19 23:59:59.999999') // 1660921199.999999

1660921199.999999 -> 2022년 8월 19일 금요일 오후 11:59:59.999 GMT+09:00

소숫점이 사실 .999 를 넘어가면 값이 모두 동일하게 보이긴 합니다. 이건 Precision 의 영역인 것 같네요.

1660921199.99 -> 2022년 8월 19일 금요일 오후 11:59:59.990 GMT+09:00 1660921199.999 -> 2022년 8월 19일 금요일 오후 11:59:59.999 GMT+09:00 1660921199.9999 -> 2022년 8월 19일 금요일 오후 11:59:59.999 GMT+09:00 1660921199.99999 -> 2022년 8월 19일 금요일 오후 11:59:59.999 GMT+09:00 1660921199.999999 -> 2022년 8월 19일 금요일 오후 11:59:59.999 GMT+09:00

DolphaGo / TIL

[MySQL] datetime save 시 주의해야 할 점 (feat. Fractional Second, digit) #116