MySQL의 날짜 시간 타입(5.7 기준)

TL;DR

• TIMESTAMP 타입은 Time zone을 다뤄야 하는 경우 유용할 수 있다. 하지만 최대로 표현가능 한 범위가 'UTC 2038년 1월 19일' 까지이므로, 사용에 주의를 요한다.
  • TIMESTAMP 타입은 MAX_DB(5.7.22 에서 deprecated로 전환) 모드가 아닌이상, UTC 형태로 저장되고, 커넥션의 타임존에 따라 변환이 수행된다.
• DATETIME 은 Time zone을 지원하지 않으므로, 이를 대응하기 위한 방법에 대해서 고민해야한다.

MySQL에는 날짜 시간을 다루는 타입이 세 가지 있습니다.
DATE, DATETIME, TIMESTAMP 가 바로 그 세 가지 타입입니다.

DATE 와 DATETIME

DATE 와 DATETIME 은 타입명에서 알다시피 날짜와 날짜 시간을 저장하기 위한 타입입니다. MySQL에서는 'YYYY-MM-DD' 포맷을 통해 DATE 를 표현할 수 있으며, 'YYYY-MM-DD hh:mm:ss' 포맷을 통해 DATETIME 을 표현할 수 있습니다.

여기서 구분자는 다른 구두점으로도 변경가능하며, 이는 유의해야 합니다.

DATE 의 지원 가능 범위는 '1000-01-01' ~ '9999-12-31' 이고, DATETIME 의 지원 가능 범위는 '1000-01-01 00:00:00' ~ '9999-12-31 23:59:59' 까지입니다.

DATETIME 과 TIMESTAMP

DATETIME 과 TIMESTAMP 는 둘다 날짜 시간을 표현하기 위한 타입입니다.

하지만 이 둘은 몇가지 차이점이 있습니다.

차이점

1. 표현 가능한 범위의 차이가 있습니다.
   • DATETIME 은 위에서 설명한 범위를 표현가능합니다.
   • TIMESTAMP 는 '1970-01-01 00:00:01' UTC ~ '2038-01-19 03:14:07' UTC 범위를 표현 가능합니다.
2. 데이터 저장 방식의 차이가 있습니다.
   • DATETIME 은 커넥션의 시간대의 정보를 그대로 저장하고, 조회시에도 그대로 반환합니다.
   • TIMESTAMP 는 커넥션의 시간대 정보를 바탕으로 UTC 로 저장하고, 조회 시에는 UTC 로 저장된 정보를 바탕으로 커넥션의 시간대에 따라 변환해서 반환합니다.
   • 이 차이 때문에 동작이 달라질 수 있습니다. Global 서비스에서는 큰 문제로 이어질 수 있습니다.
   • 참고로 커넥션마다 time_zone을 지정할 수 있습니다.

둘은 날짜 시간을 표현하므로 소수단위초에 대한 지원도 가능합니다. 이 때의 포맷은 'YYYY-MM-DD hh:mm:ss[.fraction]' 형태이며, 소수단위는 다른 구분자와는 다르게 반드시 . 을 구분자로 해야합니다.

유효하지 않은 값에 대한 대응

MySQL 에서는 유효하지 않은 값에 대한 대응 방법을 여러가지 제공합니다. SQL 모드가 ZERO_DATE 를 허용하는 경우 해당 유형의 'zero' 값으로 변환됩니다. e.g.) '0000-00-00', 0000-00-00 00:00:00

이 동작은 strict SQL 모드와 NO_ZERO_DATE SQL 모드 중 어느것이 활성화되어 있는가에 따라 달라질 수 있습니다.

월, 일 값의 유효성에 대한 대응

strict SQL 모드상에서는 '2004-04-31' 과 같은 유효하지 않은 날짜는 '0000-00-00' 으로 변환되고, 경고가 발생합니다.

이러한 날짜를 허용하려면 ALLOW_INVALID_DATES 옵션을 활성화 하면 됩니다.

MySQL 에서는 일 또는 월 값이 '0' 인 TIMESTAMP 값은 허용하지 않습니다. 이 규칙 역시 SQL 모드상에서 특수한 'zero' 값을 허용하는 경우 예외적으로 허용될 수 있습니다.

이 동작들 모두 strict SLQ 모드와 NO_ZERO_DATE SQL 모드의 활성화 여부에 따라 달라질 수 있습니다.

두자리 연도 변환 방식

MySQL 에서는 두자리 연도를 사용할 경우 변환하는 기능이 제공됩니다. 이 때는 100 단위의 연도를 모르기 때문에 다음과 같은 규칙으로 두자리 연도 값을 해석합니다.

• 연도가 00-69 인 경우 2000-2069 로 해석합니다.
• 연도가 70-99 인 경우 1970-1999 로 해석합니다.

TIMESTAMP 는 값의 한계가 명확하고, DATETIME 은 Time zone을 지원하지 않는데 어떻게 해야할까?

일단 TIMESTAMP 의 사용은 아무래도 값의 한계가 존재하므로, 무작정 값을 저장하는데 어려움이 있을 수 있습니다.

따라서 다음과 같이 다룰 수 있습니다.

• local_time DATETIME
• utc_time DATETIME
• time_zone VARCHAR(X): X는 보통 64

utc_time 을 별도로 저장하므로써, global 지원이 필요한 경우 utc time을 가지고 변환을 해서 사용이 가능합니다.

더 좋은 방법이 있다면 공유해주시면 감사하겠습니다.

참고자료

• mysql 5.7 datetime 공식문서
• stackoverflow datetime time zone 문제