DATETIME vs TIMESTAMP 둘 중 어느것이 더 나을까?

 

프로젝트를 하면서 맨 처음에는 시간 데이터로 DATETIME을 사용했습니다.

 

MySQL기준으로 시간을 나타내는 대표적인 타입으로 DATETIME과 TIMESTAMP 2종류가 존재합니다.

어떻게 더 나을지 테스트한 결과를 포스팅해보려고 합니다.

---

1. MySQL에서의 시간 데이터

MySQL공식문서에 따르면 시간 데이터는 총 3가지가 존재합니다.

간단하게 특징별로만 소개하자면 아래와 같습니다.

 

DATE

 

• 시간부분 없이 날짜로만 'YYYY-MM-DD' 형식으로 사용된다.
• 지원되는 범위 '1000-01-01'에서 '9999-12-31'까지 가능하다.
• 저장용량은 3byte이다.

그렇다면 시간까지 포함하는 DATETIME과 TIMESTAMP의 차이는 무엇일까요?

 

DATETIME

 

• 날짜 및 시간 부분 모두 포함해서 'YYYY-MM-DD hh:mm:ss' 형식으로 사용된다.
• 지원되는 범위는 '1000-01-01 00:00:00'에서 '9999-12-31 23:59:59'까지이다.
• 저장용량은 5byte이다.
• 저장되는 시간에 timezone정보가 영향받지 않는다.

 

TIMESTAMP

• 날짜 및 시간 부분 모두 포함해서 'YYYY-MM-DD hh:mm:ss' 형식으로 사용된다.
• 지원되는 범위는 '1970-01-01 00:00:01' UTC에서 '2038-01-19 03:14:07' UTC까지이다.
• 저장용량은 4byte이다.
• 값을 저장하기 위해 현재 시간대에서 UTC로 변환하고 검색을 위해 UTC에서 현재 시간대로 다시 변환한다.
• 시스템의 timezone설정에 따라 현재 시간이 저장된다.

구글링을 하다보면 글로벌 서비스로 확장을 할 경우 Timezone에 따라서 현재 시간이 저장되는 TIMESTAMP가 낫지만,

일반적으로는 지원범위가 더 넓은 DATETIME을 사용한다고 합니다.

 

그렇다면 글로벌 서비스를 할 경우에는 지원되는 범위를 포기하고 TIMESTAMP를 사용해야할까요?

시간데이터를 저장할때 트레이드오프가 발생하는 지 timezone 테스트를 해보겠습니다.

2. TIMEZONE 테스트

 

MySQL Timezone을 확인하는 방법은 아래와 같습니다.

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone;

 

별도로 타임존 설정을 하지 않았다면 아래와 같이 나타날 것입니다.

여기서 SYSTEM은 서버 시스템 시간을 타임 존으로 사용하고 있다는 의미입니다.

 

• @@GLOBAL.time_zone
  • MySQL 서버의 글로벌 시스템 시간대를 나타냅니다. 서버 전체에 적용되며 모든 세션에 영향을 미칩니다.
• @@SESSION.time_zone
  • 현재 MySQL 세션의 시간대를 나타냅니다. 세션은 사용자가 MySQL 서버에 연결할 때 생성되고 연결이 끊기면 소멸합니다. 따라서 세션별로 서로 다른 시간대 설정이 가능합니다.
• @@system_time_zone
  • MySQL 서버가 호스트되는 운영 체제(시스템)의 시간대를 나타냅니다.

 

테스트를 해보기 위해 임시 테이블를 만들어보겠습니다.

DROP TABLE IF EXISTS datedemo;

CREATE TABLE datedemo
(
    my_date_time DATETIME NOT NULL COMMENT '데이터타임',
    my_time_stamp TIMESTAMP NOT NULL COMMENT '타임스탬프'
);

desc datedemo;

 

 

현재 시간 기준으로 데이터를 넣으면 동일한 값이 나옵니다.

INSERT datedemo VALUES (NOW(), NOW());

 

timezone 적용 전

• my_date_time : 시스템 설정 시간 기준
• my_time_stamp : UTC기준

시스템으로 설정되어 있을 경우 datetime은 현재 한국 시간 기준(현재 시간 2024-02-11 02:41:10)으로 나오고 timestamp는 UTC 기준으로 나옵니다. 아래 사진은 포스팅하던 와중에 캡쳐해서 저장된 시간과 다르지만 UTC 시간은 아님을 확실히 알 수 있습니다.

Mac 시간 날짜 캡처 화면

 

한국 시간으로 맞추려면 timezone을 아래처럼 바꿔줘야 합니다.

set TIME_ZONE = 'Asia/Seoul';

timezone 적용 후

• my_date_time : 시스템 설정 시간 기준
• my_time_stamp : Asia/Seoul 기준

사진을 보면 timezone을 설정한 이후로 timestamp칼럼의 데이터가 한국 시간으로 변경된것을 볼 수 있습니다.

이로 인해 DATETIME은 timezone과 독립적이고, TIMESTAMP는 종속적이라는 것을 확인했습니다.

 

이 차이를 알아보고 저는 확장성을 위해서 글로벌하게 사용할 수 있게하려면 timezone에 의존하는 TIMESTAMP를 사용해야 한다고 생각했습니다. 그럼 지원범위에 대한 이슈는 어떻게 될까요?

 

2. TIMESTAMP의 Y2K38 이슈

 

지원범위가 '2038-01-19 03:14:07' UTC까지인 TIMESTAMP는 10년 이상 서비스를 지속하기에는 어려움이 있습니다. 하지만 현재 서비스를 운영중인 회사에서도 TIMESTAMP를 사용하는 곳이 분명히 있을텐데, 그럼 그런 회사들의 서비스들은 전부 38년이 되면 시간 장애가 발생하는 것일까요?

 

이처럼 2038년 1월 19일 이후로 발생하는 시간이 제대로 표현이 되지 않는 장애를 Y2K38이라고 하는데

32비트 정수형을 이용한 날짜와 시간 표현의 오버플로우가 발생해서 다시 최소값으로 돌아가는 문제입니다.

 

MySQL에서도 사용자로부터 여러 문의가 들어왔을 것이라고 예상했고, 어떤 조치를 취하지 않았을까 싶어서 MySQL공식문서(Changes in MySQL 8.0.28) 를 찾아보았습니다.

해당 이슈는 MySQL 버전 8.0.27까지 발생했지만, 8.0.28버전 이후부터 '3001-01-18 23:59:59' 까지 표현되도록 업데이트를 했다고 합니다.

 

실제로 SQL상으로 테스트해본 결과

• 8.0.27버전은 2038-01-19 03:14:07에서 1초라도 추가되면 1970-01-01로 돌아가지만
• 8.1.0버전은 2038-01-19 03:14:07 이후의 날짜도 잘 나오는 것을 확인했습니다.
  (8.0.28버전은 제 OS에서 workbench가 실행이 안되서 기존에 사용하던 8.1.0버전으로 테스트했습니다.)

8.0.27버전

 

8.1.0 버전

 

이를 기반으로 TIMESTAMP를 사용한다고 해도 10년뒤에 장애가 발생하지 않겠다는 것을 알게 되었습니다.

 

하지만 이렇게 TIMEZONE을 설정한다는건 비즈니스 로직인데 데이터 저장소에서 처리하는게 맞을지 의문이 들었습니다.

MySQL이 아니라 애플리케이션 레벨에서 설정하면 어떨까요?

 

단지 '글로벌 확장'을 위해 TIMESTAMP를 선택한다고 했을 때 문제가 되는 상황이 2가지 있습니다.

 

1. set으로 설정한 timezone은 DB가 재시작되거나 꺼지면 다시 세팅해야하는 1회용 세팅입니다.

2. mysql에서 timezone 설정한 걸 모르고(혹은 잊어버리고) 애플리케이션에서도 timezone 설정할 경우 

 

1번의 경우에는 mysqld.cnf파일에서 default 타임존을 설정해서 재시작하더라도 타임존을 그대로 유지할 수 있습니다.

그렇다면 문제가 되는 상황은 2번인데, 애플리케이션 레벨에서도 TIMEZONE을 설정할 수가 있습니다.

 

 

3. 어플리케이션에서의 TIMEZONE 설정

 

Spring Boot 애플리케이션을 띄울때 Timezone 설정을 해줄 수 있습니다.

@SpringBootApplication
public class TimeZoneApplication {

    @PostConstruct
    void started() {
    	// Asia/Seoul 등등
        TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
    }

    public static void main(String[] args) {
        SpringApplication application = new SpringApplication(ApiApplication.class);
        application.setLogStartupInfo(false);
        application.run(args);
    }
}

public class DateTimeUtils {

    public static LocalDateTime nowFromZone() {
    	// "America/New_York" 
        return ZonedDateTime.now(ZoneId.of("Asia/Seoul")).toLocalDateTime();
    }
}

 

 

 

다시 MySQL의 timezone을 System인 상태로 변경해두고

애플리케이션에서 timezone을 변경해서 저장하면 어떻게 될지 테스트해보겠습니다.

@RestController
@RequiredArgsConstructor
public class TimeTestController {

    private final DateDemoRepository dateDemoRepository;
    @GetMapping("/")
    public Map<String, LocalDateTime> timeTest() {
        LocalDateTime dateTime = DateTimeUtils.nowFromZone();
        LocalDateTime timeStamp = DateTimeUtils.nowFromZone();
        dateDemoRepository.insertTime(dateTime, timeStamp);
        return Map.of("dateTime", dateTime, "timeStamp", timeStamp);
    }
}

 

@Mapper
public interface DateDemoRepository {
    void insertTime(LocalDateTime mydatetime, LocalDateTime mytimestamp);
}

 

<mapper namespace="com.flab.offcoupon.repository.DateDemoRepository">
    <insert id="insertTime">
        INSERT INTO  datedemo (my_date_time, my_time_stamp)
        VALUES (#{mydatetime}, #{mytimestamp})
    </insert>
</mapper>

 

 

my_date_time

1. 현재 시스템 시간 기준
2. Asia/Seoul 기준
3. America/New_York기준

my_time_stamp

1. UTC기준
2. Asia/Seoul 기준
3. America/New_York기준

사진에서 2번째 3번째 행을 보면 각각 애플리케이션에서 설정한 대로 한국과 미국 시간으로 잘 들어오는 것을 확인할 수 있습니다.

 

여기서 MySQL의 timezone을 한국으로 변경해보겠습니다.

 

my_date_time은 변함이 없는데, my_time_stamp만 바뀌었습니다.

 

my_time_stamp

1. UTC기준 →   Asia/Seoul기준
2. Asia/Soul 기준(애플리케이션)  → Asia/Seoul(애플리케이션) + Asia/Seoul(MySQL) : + 9시간
3. America/New_York기준(애플리케이션) → America/New_York(애플리케이션) + Asia/Seoul(MySQL) : + 9 시간

2번과 3번에서 애플리케이션 레벨에서 설정한 timezone으로 저장된 데이터가 +9시간씩 추가가 된것을 볼 수 있습니다.

이와같은 상황은 MySQL의 timezone을 미국 시간대로 설정해도 나타납니다.

 

my_time_stamp

1. UTC기준 →   America/New_York기준
2. Asia/Soul 기준(애플리케이션)  → Asia/Seoul(애플리케이션) + America/New_York(MySQL) : - 5시간
3. America/New_York기준(애프리케이션) → America/New_York(애플리케이션) + America/New_York(MySQL) : - 5시간

 

이렇게 DB에서도 locale을 관리하고 애플리케이션에서도 locale을 관리하게 되면 데이터베이스와 애플리케이션 간의 locale의 차이가 발생하게 됩니다. 이런걸 일일히 맞춰야하기 때문에 오히려 더 혼란스럽고 애매해질 수 있을 것 같습니다.

 

4. 결론은 DATETIME 선택

지금 진행하는 프로젝트가 이벤트를 열어서 쿠폰을 발행하는 주제와 관련해서 지금까지 테스트 해본 내용을 바탕으로 고려해보자면,

오전 10시에 이벤트를 연다고 했을때 TImezone 설정에 따라서 새벽 2시에 이벤트가 시작될 수 도 있습니다.

 

글로벌 서비스로 인해 여러 국가에서 DB Clustering을 할 경우 (데이터베이스가 여러 국가에서 존재하는 경우) timezone이 적합하다고 하지만, 개인적으로 생각했을때 애플리케이션과 DB의 Timezone이 결합되면 더 혼란을 줄 수 있기 때문에 하나로 고정해야겠다고 판단했습니다.

 

그래서 결국 MySQL에서는 timezone에 의존하지 않는 DATETIME을 선택하되, 애플리케이션에서 클라이언트의 locale에 따라 timezone을 설정하는게 좋다고 생각합니다.