[MySQL|Connector/J] 시간대를 포함한 시간데이터 처리

이 글은 MySQL 8, Connector/J 8 버전을 기준으로 작성됩니다.

MySQL 8

MySQL 8.0.19부터는 TIMESTAMP, DATETIME 테이블에 삽입할 때 시간대 오프셋을 지정할 수 있습니다.

TIMESTAMP

UTC에서 UTC까지 범위 ( '1970-01-01 00:00:01''2038-01-19 03:14:07' )

저장을 위해 세션 시간대에서 UTC로 변환하고 검색을 위해 UTC에서 세션 시간대로 다시 변환합니다. 

저장한 후 시간대를 변경하고 검색하는 경우 검색된 값은 저장한 값과 다릅니다. 이는 양방향 변환에 동일한 시간대가 사용되지 않았기 때문에 발생합니다.

 

DATETIME

지원 범위 ( '1000-01-01 00:00:00''9999-12-31 23:59:59' )

TIMESTAMP, DATETIME 시간대 insert 예시는 문서 하단에서 확인가능.

DATETIME은 UTC로 변환이 발생하지 않습니다. 세션 시간대로 저장합니다.

Connector/J 8

TIMESTAMP는 인스턴트를 저장하도록 설계된 유일한 MySQL 데이터 유형입니다. 서버는 시간 인스턴트를 보존하기 위해 필요할 때 수신 또는 발신 시간 값으로 시간대 변환을 적용합니다.

수신 값은 서버별로 연결 세션의 시간대에서 UTC로 변환되어 저장되며, 발신 값은 UTC에서 세션 시간대로 변환됩니다.
MySQL 8.0.19부터는 TIMESTAMP 값을 저장할 때 시간대 오프셋을 지정할 수도 있습니다. 이 경우 TIMESTAMP 값은 세션 시간대 대신 지정된 오프셋에서 UTC로 변환됩니다. 한 번 저장하면 원래 오프셋 정보가 더 이상 보존되지 않습니다.

 

옵션

8.0.23 부터 적용 됩니다.

어플리케이션 시간대와 MySQL 시간대가 동일한 경우 : 시간 순간이 자연스럽게 보존되며 시간대 변환이 필요하지 않습니다. ex) MySQL : Asia/Seoul, JVM : Asia/Seoul

 

어플리케이션 시간대와 MySQL 시간대가 다른 경우 : 아래 방법 중 하나를 수행합니다.

1. 서버에서 세션 시간대의 값을 쿼리하고 세션 시간대와 JVM 시간대 간의 이벤트 타임스탬프를 변환합니다. (SERVER)
2. 서버의 세션 시간대를 JVM 시간대로 변경합니다. 이후에는 시간대 변환이 필요하지 않습니다. (LOCAL)
3. 서버 세션 시간대를 사용자가 지정한 원하는 시간대로 변경한 다음 JVM 시간대와 사용자가 지정한 시간대 사이의 타임스탬프를 변환합니다.(user-defined-time-zone)

아래 예시는 TIMESTAMP 기준으로 작성됨.

1.prepareInstants=true&connectionTimeZone=SERVER

요청한 JVM 시간대 : UTC+2
서버 시간대: UTC+1 

JVM -> DB
Connector/J가 서버로 보낼 때 서버 시간대로 변환 2020-01-01 01:00:00 (UTC+2에서 UTC+1로 변환한 후)
서버 내부에서 UTC로 변환 2020-01-01 00:00:00 UTC(UTC+1에서 UTC로 내부 변환 후)

DB -> JVM (서버 시간대 -> JVM 시간대 변환 필요)
서버 내부에서 UTC에서 서버 시간대로 변환 : 2020-01-01 01:00:00 (UTC에서 UTC+1로 내부 변환 후)
DB에서 위 값을 Connector/J에 넘긴 후 JVM에서 시간대 맞춰 변환
1-1)
요청한 JVM 시간대(UTC+2)에서 Connector/J에 의해 생성되고 애플리케이션에 반환된 타임스탬프 값: 2020-01-01 02:00:00 (UTC+1에서 UTC+2로 변환 후)

1-2)
다른 JVM 시간대(UTC+3)에서 Connector/J에 의해 생성되어 애플리케이션에 반환된 타임스탬프 값: 2020-01-01 03:00:00 (UTC+1에서 UTC+3으로 변환한 후)

ResultSetImpl.class

2. ConnectionTimeZone=LOCAL& forceConnectionTimeZoneToSession=true

요청한 JVM 시간대 : UTC+1 
서버 시간대 : UTC+2 --> Connector/J에 의해 UTC+1로 수정

JVM -> DB
Connector/J가 서버로 보낸 타임스탬프(UTC+1) : 2020-01-01 00:00:00+01:00
서버 내부에서 UTC로 변환 2020-01-01 00:00:00 UTC(UTC+1에서 UTC로 내부 변환 후)

DB -> JVM (변환이 필요하지 않음)
1-1)
서버 세션으로 검색되는 타임스탬프 값(Connector/J에 의해 설정된 UTC+1): 2020-01-01 00:00:00+01:00(UTC에서 UTC+1로 내부 변환 후)
요청한 JVM 시간대(UTC+1)에서 Connector/J에 의해 생성된 타임스탬프 값: 2020-01-01 00:00:00+01:00

1-2)
변경된 서버 세션으로 검색된 타임스탬프 값(Connector/J에 의해 UTC+3으로 수정된 시간대): 2020-01-01 00:00:00+03:00(UTC에서 UTC+3으로 내부 변환 후)
변경된 JVM 시간대(UTC+3)에서 Connector/J에 의해 생성된 타임스탬프 값: 2020-01-01 00:00:00+03:00

ResultSetImpl.class

 

3. prepareInstants=true&connectionTimeZone=Europe/Berlin&forceConnectionTimeZoneToSession=true

JVM 시간대 : UTC+2
세션 시간대 : Europe/Berlin

JVM -> DB
클라이언트의 원본 타임스탬프(UTC+2): 2020-01-01 00:00:00+02:00
Connector/J가 서버로 보낸 타임스탬프: 2020-01-01 00:00:00+01:00(JVM 시간대 -> 세션 시간대 변환 후)
( UTC+2 -> UTC+1)
서버 내부에 저장된 값: 2020-01-01 00:00:00(UTC+1에서 UTC로 내부 변환 후)

DB -> JVM
세션 시간대 (커넥터/J에 의해 시간대가 Europe/Berlin로 수정됨): 2020-01-01 00:00:00+01:00
(UTC에서 UTC+1로 내부 변환 후)
JVM 시간대에서 Connector/J에 의해 생성되어 애플리케이션에 반환된 타임스탬프 값: 2020-01-01 00:00:00+02:00
(세션 시간대 -> JVM 시간대 변환 후)
(UTC+1 -> UTC+2)

ResultSetImpl.class

JAVA8 TEST (OffsetDateTime, ZonedDateTime with DATETIME, TIMESTAMP)

1. prepareInstants=true&connectionTimeZone=SERVER

2. ConnectionTimeZone=LOCAL&forceConnectionTimeZoneToSession=true

3. connectionTimeZone=Europe/Berlin&forceConnectionTimeZoneToSession=true

위 세가지 케이스 예시에서 db 캡쳐 이미지를 보면 timestamp 는 모두 UTC로 변환되어 저장된 것을 알 수 있고, datetime는 서버세션 timezone으로 저장된 것을 알 수 있다.

 

참고 :

https://dev.mysql.com/doc/relnotes/connector-j/en/news-8-0-23.html

https://dev.mysql.com/blog-archive/support-for-date-time-types-in-connector-j-8-0/

https://dev.mysql.com/doc/connector-j/en/connector-j-time-instants.html

https://dev.mysql.com/doc/connector-j/en/connector-j-connp-props-datetime-types-processing.html

https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

https://dev.mysql.com/doc/refman/8.0/en/datetime.html