[MySQL] UTC 시간대, date_format() 결과값이 다른 이유

UTC 시간대, date_format() 결과값이 다른 이유

 

💡 개요

---

다음은 2020년 7월의 구매 유저 수를 구하기 위해 작성한 쿼리입니다.

그러나 위의 두 쿼리는 의도와는 다르게 결과값이 서로 달랐는데요.

원인 파악을 위해 아래와 같은 쿼리문을 작성해보았습니다.

 

🔍 원인 분석

---

자세히 확인해 보니 2020년 7월 31일 15시부터는 2020년 8월로 포맷팅 되고 있었습니다. 이렇듯 잘못 포맷팅 되는 행들이 있어서 7월에 해당됨에도 불구하고 결과값에서는 제외된 행들이 있었던 겁니다.

그렇다면 왜 2020년 7월 31일 15시부터는 2020년 8월로 포맷팅 되는지 알아보았습니다.

 

이를 이해하기 위해서는 먼저 다음 사항에 대해 알아야 했습니다.

✅ 시간대(time zone) : 지구상에서 사용되는 서로 다른 지역의 표준시로 한국은 UTC+9 시간대 사용

✅ UTC: 협정 세계시로 전 세계의 표준 시간을 말하며 '0'으로 설정되어 있음.

✅ 시간 뒤에 '+00:00'은 이 시간이 UTC에 맞춰져 있음을 의미

✅ 데이터베이스에 저장된 DATETIME 값은 시간대 정보 없이 순수한 날짜와 시간으로만 표현된다. 'DATETIME' 타입은 시간대를 고려하지 않는 절대 시간을 저장한다. 따라서, 데이터베이스 서버의 시간대 설정이나 클라이언트(예: MySQL Workbench)의 시간대 설정과 무관하게 동일하게 해석된다.

✅ 데이터베이스 서버의 시간대 설정이 한국 시간대(KST, UTC+9)로 설정되어 있다면, 서버는 UTC 시간대를 기준으로 한 입력 값을 서버 시간대에 맞춰 변환하여 처리할 수 있습니다. 그러나 이는 데이터를 저장하거나 처리할 때 발생하는 변환일 뿐, 저장된 값 자체에는 시간대 정보가 포함되지 않는다.

위의 내용을 알고 나니 이해가 잘 되지 않던 부분들이 이해가 되었습니다. 다시 정리해 보겠습니다.

 

😊 결론

---

🔍 데이터베이스 서버의 시간대 설정은 '대한민국 표준시'였습니다. 이는 UTC + 9 시간대입니다.

✅ DATETIME()을 사용해서 저장된 값을 처리하게 될 때, 입력값이 UTC에 맞춰져 있다면 데이터베이스 서버는 이를 서버의 시간대에 맞춰 변환해서 보여준다.

  ✔️ 변환해서 보여주기 때문에, DATE_FORMAT('2020-07-31 15:00:00+00:00', '% Y-%m')의 값이 '2020-08'이었던 것.

  ✔️ UTC에 맞춰진 값이 아니라면 지정된 시간대가 없는 것이기에 변환되지 않는다. 

  ✔️DATE_FORMAT('2020-07-31 15:00:00', '% Y-%m')의 값은 '2020-07'

  ✔️ 다음과 같이 변환될 값에 9 hour을 빼서 값을 조정하면 간단히 해결할 수 있다.

 

✅ UTC 시간대를 기준으로 저장된 값은 데이터베이스 서버에서 데이터를 저장하거나 처리할 때만 변환되는 것이다. 값 그 자체는 시간대 정보를 포함하지 않는다.

  ✔️ 맨 처음 쿼리의 조건문 "WHERE purchased_at >= '2020-07-01' AND purchased_at < '2020-08-01'" 에서는 저장된 purchased_at 값 그대로 7월에 해당되는 행들을 모두 조회한 이유.

 

이상으로 UTC 시간대와 DATE_FORMAT() 함수 사용 시 결과값이 다른 이유에 대해 알아보았습니다.