UTC 와 TIMEZONE

날짜 함수와 연산

UNIX TIMESTAMP 관련 문서

※ 주의
날짜 연산을 할 때 MySQL 내부적으로 사용하는 방식은 주로 UNIX_TIMESTAMP 방식(1970년 1월 1일부터 경과한 초)을 기준으로 계산

MySQL은 날짜를 저장할 때, 이를 UNIX 시간(또는 POSIX 시간)으로 변환하여 내부적으로 처리할 수 있습니다. 
UNIX 시간은 1970년 1월 1일 00:00:00 UTC를 기준으로 경과된 초 단위의 시간입니다.
예를 들어, '2025-03-20 12:00:00'과 같은 날짜는 MySQL 내부에서 1970-01-01 00:00:00부터 경과된 초로 변환되어 처리됩니다.

날짜에 일수, 월수, 연수 등을 더하거나 빼는 경우, 
MySQL은 해당 날짜를 UNIX_TIMESTAMP로 변환한 뒤, 
해당 값을 기준으로 더하거나 뺀 후 다시 날짜 형식으로 변환합니다.

왜 UTC가 중요한가?

전 세계 동기화: 
인터넷과 글로벌 시스템에서는 시간을 동기화해야 할 필요가 있습니다. 
예를 들어, 서버의 로그, 데이터베이스의 타임스탬프, 이메일 발송 시간 등이 전 세계적으로 정확하게 일치해야 합니다. 
UTC는 모든 지역에서 동일한 기준이므로 이를 사용하면 시간의 일관성을 유지할 수 있습니다.

국제 항공이나 우주 탐사와 같은 분야에서도 정확한 시간 기준이 필요하며,
UTC가 기준으로 사용됩니다.

실제 사용 문제

32번 )
doctors 테이블에서 현재 날짜 기준으로 5년 이상 근무(hire_date)한 의사 수를 계산하는 쿼리를 작성해주세요!

SELECT 
    major,
    COUNT(*) AS five_years_hire_date
FROM doctors
WHERE TIMESTAMPDIFF(YEAR, hire_date,  CURDATE()) >= 5
GROUP BY major;

하지만 이건 문제가 있습니다. 연도만 계산 합니다.
즉 TIMESTAMPDIFF YEAR 로 UNIT 을 잡았다면 2025-03-20 이 기준이며,
입사날이 2019년 1월, 2019년 12월 모두 5년이상 근무가 되어버립니다.

SELECT 
    major,
    COUNT(*) AS five_years_hire_date
FROM doctors
WHERE TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) >= 60
GROUP BY major;

아래와 같이 수정하면 60개월 이 되고 일로 DAY UNIT = 1825로 바꾸면 일 수가 됩니다.

근데 또 궁금한게 생기더라구요.

SELECT UNIX_TIMESTAMP('2025-03-20 12:00:00'),
FROM_UNIXTIME(1742472000);

저는 UTC 는 세계 협정시인걸 배웠습니다.
근데 UNIX_TIMESTAMP 로 저 날짜는 한국 입니다.

Q) 만약 저 날짜를 한국에서 변환 후 다른 시차가 나는 나라에서 실행 시킨다면 어떤일이 발생할까?
과연 똑같이 2025-03-20 12:00:00 으로 나올까?

그래서 한번 실험을 해봤습니다.
내 서버의 시계를 미국 동부로 바꾸고 변경을 해보면?

1742486400 이 나옵니다. 이 시간을 다시 한국으로 변경해서 바꿔보도록 하겠습니다.

2025-03-20 12:00:00 < 미국 동부 기준
2025-03-21 01:00:00 < 한국 기준
13시간 정도가 나이 차는 게 보입니다.

공식 문서를 찾아보니, MySQL - TimeZONE 타임존 기능이 있었습니다.
여기서는 오래된 날짜를 최신으로 바꾸는 방법 UTC 변환 등등 여러가지 설명이 있는데 정리해보면 다음과 같습니다.

1. UNIX_TIMESTAMP() 로 날짜를 보내주면 그걸 UTC 로 변환 합니다.
   EX) 2025-03-20 12:00:00 -> 2025-03-20 03:00:00 으로 변환 후 초로 변환
2. UTC 로 변환된 (초) 를 다시 실행시키게 되면 현재 DB 타임존을 인식하여 그만큼 초를 추가 합니다.
   예를 들어, 'Asia/Seoul' 이라고 되어 있다면 UTC 기준 +9 즉 9시간을 추가해서 반환 합니다.
3. 그렇게해서 FROM_UNIXTIME() 에 UTC 를 입력하면 TIMEZONE 에 따라서 변환 됩니다.

원래 알고 있던 사실

• 함수라고는 날짜 변환, 추가, 차감만 있는 줄 알았다.
• 서버 컴퓨터의 시간으로 시간이 저장된다.

새롭게 알게 된 사실

• 날짜를 변환, 추가, 차감 말고도 년 수, 개월 수 등등 많은 것이 있다는 걸 알았다.
• UTC, TIMEZONE 등등 이런 기능이 있을 거라곤 상상도 못했다.
• 서버 컴퓨터의 시간이 아니다 UTC(세계 협정시) 를 기준으로 저장하고 언제든 변환이 가능하다는 걸 알았다.
• 내부기능 에서도 타임존이라는 기능이 작동하고 있으며, 이에 따라 날짜 데이터를 변환이 가능하다.