TIMESTAMP vs DATETIME (feat. ORM)

TIMESTAMP와 DATETIME의 공통점

• date뿐만 아니라 time까지 저장한다.(예: 2023-07-12 00:50:00)
• microseconds(0.000000초)까지 표현 가능.

차이점

• ORM이 DDL을 생성할 때, 기본적으로, Prisma는 DATETIME으로, JPA는 TIMESTAMP로(❌) 생성한다.
• 범위가 다르다.
  DATETIME: '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.499999'
  TIMESTAMP: '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.499999'
• DATETIME과는 달리, TIMESTAMP의 경우 저장할 때 UTC로 변환되어 저장되었다가 읽을 때 현재 타임존으로 변환되어 조회된다.

실험

TIMESTAMP 타입 컬럼에 저장한 값이 정말 다르게 보이는지 확인한다.

1. 테이블을 하나 만들고 거기에 DATETIME 타입의 컬럼 하나와 TIMESTAMP 타입의 컬럼 하나를 만든다.

CREATE TABLE `test` (
  `id` int NOT NULL,
  `dt` datetime(3) DEFAULT NULL,
  `ts` timestamp(3) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

2. 타임존을 한국 시간(GMT+9)으로 설정한 후 두 컬럼에 같은 시간이 들어가도록 삽입한다.
   (참고로, 아래 타임존 변경 쿼리가 DB의 타임존을 변경해 버리지는 않는다. 세션에만 해당되기에 재연결 시 초기화된다.)

SET @@session.time_zone = '+09:00';

INSERT INTO test (`id`, `dt`, `ts`)
VALUES (1, '2023-07-12 01:29:00', '2023-07-12 01:29:00');

3. 조회하여 두 컬럼의 시간 값이 같음을 확인한다.

SELECT * FROM test;
| 1 | 2023-07-12 01:29:00 | 2023-07-12 01:29:00 |

4. 타임존을 변경한 후 다시 조회해 본다.

SET @@session.time_zone = '+00:00';

SELECT * FROM test;
| 1 | 2023-07-12 01:29:00 | 2023-07-11 16:29:00 |

5. DATETIME 타입 컬럼에 저장했던 값은 그대로이지만, TIMESTAMP 타입 컬럼에 저장했던 값이 타임존에 따라 다르게 보이는 것을 확인할 수 있다.

ORM

Prisma(4.12.0)

모델의 필드 타입이 DateTime이면 기본적으로 MySQL의 DATETIME으로 만들어짐.
모델의 필드에 추가로 @db.Timestamp(x)을 붙이면 TIMESTAMP(x)으로 매핑됨.
공식문서 - DateTime

JPA(3.1.0, 2.2.3)

Java LocalDateTime이면 DDL 생성 시 MySQL의 DATETIME으로 만들어짐.
(얘는 TIMESTAMP일 것 같았는데 아니었어)

JPA와 LocalDateTime을 사용할 때의 문제

애플리케이션이 돌고 있는 PC 타임존: Asia/Seoul (GMT+9)
애플리케이션에서 DB 연결 시 타임존: UTC
(예: spring.datasource.url=jdbc:mysql://127.0.0.1:53306/test?serverTimezone=UTC)
저장하는 값: 2023-07-12 13:00:00 (GMT+9)
DB 타임존: Asia/Seoul

저장 결과 (DB에서 조회 시)
DATETIME: 2023-07-12 04:00:00 ❓ 한국 시간이 아닌데

• 애플리케이션에서 DB 연결 시 타임존을 UTC로 설정했기 때문에 9시간 빼고 저장됨.
  (애플리케이션이 돌고 있는 PC 타임존이 GMT-7이면 7시간 더하고 저장됨)
• 애플리케이션에서 조회할 때는 저장했던 값(2023-07-12 13:00:00) 그대로 잘 조회됨.⭕️
• DB에 UTC로 저장해 놓는다고 생각하면 제대로 저장한 것 같음.
• 그러나 DB 타임존이 UTC가 아니라 Asia/Seoul인데 그렇게 생각하는 게 맞을까?

TIMESTAMP(UTC): 2023-07-11 19:00:00 ❓ 뭐지 이 뜬금없는 값은
TIMESTAMP(GMT+9): 2023-07-12 04:00:00 ❓

• DB 타임존이 Asia/Seoul이기 때문에 2023-07-12 04:00:00을 GMT+9 간주. DB에서 UTC로 조회하면 다시 9시간을 빼서 2023-07-11 19:00:00가 조회됨.
• 애플리케이션에서 조회할 때는 저장했던 값(2023-07-12 13:00:00) 그대로 잘 조회됨.⭕️
• 그러나 아마도 DBA나 운영하는 입장에서는 매우매우 ❌가 아닐까 싶다.

참고

• 11.2.2 The DATE, DATETIME, and TIMESTAMP Types
• What is the difference between MySQL DATETIME and TIMESTAMP data type?