SQL Interview Prep · 강의

시간대와 타임스탬프

UTC를 저장하고 시간대를 변환하며, 면접관이 타임스탬프에 관해 자주 묻는 함정을 살펴봅니다.

시간대와 타임스탬프은(는) CoddyKit의 무료 SQL Interview Prep 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Interview Prep 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Interview Prep 강의에는 총 4개의 강의가 포함되어 있습니다.

시간대가 지원자를 혼란스럽게 하는 이유

시간대는 자신감 있는 지원자도 실수하는 부분이므로, 면접관은 지원자의 깊이를 확인하기 위해 이 주제를 질문합니다. 핵심 질문은 항상 같습니다. "여러 지역의 타임스탬프를 어떻게 저장하고 비교할 것인가요?"

전문적인 답변은 특정 함수가 아니라 원칙입니다. 모든 값을 UTC로 저장하고, 표시할 때만 가장자리에서 변환합니다. 저장 모델을 올바르게 정하면 대부분의 쿼리는 간단해집니다.

시간대가 없는 타임스탬프와 시간대가 포함된 타임스탬프
시간대 간 변환
기준값으로서의 UTC

시간대 없는 타임스탬프와 시간대 포함 타임스탬프 비교

PostgreSQL에는 두 가지 타임스탬프 유형이 있으며, 이를 혼동하는 것은 면접에서 자주 하는 실수입니다.

timestamp(시간대 없음): 연결된 시간대가 없는 벽시계 값입니다. 입력한 값을 그대로 저장합니다.
timestamptz(시간대 포함): 내부적으로 UTC로 저장됩니다. 입력할 때 세션 시간대에서 변환하고, 출력할 때 다시 변환합니다.

이름과 달리 timestamptz는 시간대를 저장하지 않습니다. 정확한 UTC 시점을 저장합니다. 이 세부 사항을 설명하면 면접관에게 좋은 인상을 줄 수 있습니다.

CREATE TABLE events (
  id          bigint,
  occurred_at timestamptz   -- recommended: an absolute instant
);

UTC로 저장하고 가장자리에서 변환하기

가장 중요한 원칙입니다. 시점을 UTC로 저장하고(timestamptz 사용), 사용자에게 표시할 때만 현지 시간대로 변환하십시오. 이렇게 하면 일광 절약 시간제에 따른 모호성을 피할 수 있고 어디서나 시간을 올바르게 정렬할 수 있습니다.

"왜 UTC인가요?"라는 질문을 받으면 이렇게 답하십시오. UTC에는 일광 절약 시간제 전환이 없으므로, 같은 벽시계 값이 두 번 나타나거나 건너뛰는 일이 없습니다. 현지 시간과는 다릅니다.

-- Display a UTC instant in a user's zone (Postgres)
SELECT occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events;

AT TIME ZONE의 두 가지 의미

AT TIME ZONE은 입력 형식에 따라 서로 정반대의 작업을 수행하기 때문에 영리하면서도 자주 함정이 되는 구문입니다:

timestamptz에 적용하면 절대 시점을 해당 시간대로 변환하고 일반 timestamp를 반환합니다(그곳의 벽시계 시간).
일반 timestamp에 적용하면 해당 벽시계 시간이 그 시간대에 있다고 해석하고 timestamptz를 반환합니다.

어느 방향으로 작동하는지 아는 것이 핵심입니다.

-- timestamptz -> local wall clock (returns timestamp)
SELECT TIMESTAMPTZ '2024-03-01 12:00:00+00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 21:00:00

-- plain timestamp interpreted in a zone (returns timestamptz)
SELECT TIMESTAMP '2024-03-01 12:00:00'
         AT TIME ZONE 'Asia/Tokyo';        -- 2024-03-01 03:00:00+00

현재 시점 가져오기

현재 시각 함수의 동작을 알아 두셔야 합니다. Postgres의 NOW()와 CURRENT_TIMESTAMP는 timestamptz를 반환합니다. 이 함수들은 문이 시작된 시점이 아니라 트랜잭션이 시작된 시점의 시간을 반환하므로, 트랜잭션이 오래 실행될 때 중요합니다.

UTC를 명시하려면 다음과 같이 변환합니다: NOW() AT TIME ZONE 'UTC'. MySQL에서는 UTC_TIMESTAMP()가 UTC를 직접 반환합니다.

SELECT
  NOW()                       AS tx_start_tz,
  NOW() AT TIME ZONE 'UTC'    AS utc_walltime;

진짜 적은 일광 절약 시간제입니다

면접관은 DST 경계 사례를 좋아합니다. 시계를 앞당길 때는 현지 벽시계의 한 시간이 존재하지 않으며, 시계를 되돌릴 때는 한 시간이 반복됩니다. 현지 시간을 저장하면 이런 시간이 모호하거나 유효하지 않게 됩니다.

UTC를 저장하면 이 문제를 완전히 피할 수 있습니다. 모든 시점이 고유하고 시간 순서가 단조롭게 이어지기 때문입니다. 'America/New_York'처럼 지역 이름을 사용하고 -05:00 같은 고정 오프셋을 사용하지 않으면, 데이터베이스가 날짜에 맞는 DST 규칙을 올바르게 적용합니다.

-- Region name applies DST automatically for the given date
SELECT TIMESTAMPTZ '2024-07-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS summer, -- EDT (-04)
       TIMESTAMPTZ '2024-01-01 12:00:00+00'
         AT TIME ZONE 'America/New_York' AS winter; -- EST (-05)

시간대가 다른 지역에서 현지 날짜별로 그룹화하기

현실적인 문제를 생각해 보겠습니다. "각 사용자의 현지 시간 기준 일일 활성 사용자"를 구하는 경우입니다. UTC 타임스탬프를 직접 날짜 단위로 자르면 UTC가 아닌 사용자의 자정 경계가 잘못됩니다.

날짜 단위로 자르기 전에 사용자 시간대로 변환해야 합니다. 변환하면 벽시계 시간이 이동하여 날짜 경계가 현지 기준에 맞춰집니다.

SELECT
  DATE_TRUNC('day', occurred_at AT TIME ZONE u.tz) AS local_day,
  COUNT(DISTINCT e.user_id)                         AS dau
FROM events e
JOIN users u ON u.id = e.user_id
GROUP BY 1
ORDER BY 1;

타임스탬프를 안전하게 비교하기

timestamptz 열을 필터링할 때는 명시적인 시점과 비교해야 하며, 가능하면 UTC 리터럴이나 오프셋이 포함된 timestamptz를 사용해야 합니다. 아무 표시가 없는 문자열과 비교하면 예측하기 어려운 세션 시간대로 해석될 수 있습니다.

이렇게 하면 누가 쿼리를 실행하더라도 비교가 모호해지지 않습니다.

SELECT *
FROM events
WHERE occurred_at >= TIMESTAMPTZ '2024-03-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2024-04-01 00:00:00+00';

Epoch와 Unix 타임스탬프

많은 시스템은 Unix epoch, 즉 1970-01-01 UTC 이후의 초 단위 시간으로 시각을 저장합니다. 면접관이 정수 열을 보여 주고 그 값을 해석해 보라고 할 수도 있습니다.

Postgres: TO_TIMESTAMP(epoch_seconds)는 timestamptz를 반환합니다.
Epoch로 되돌리기: EXTRACT(EPOCH FROM occurred_at).
MySQL: FROM_UNIXTIME() 및 UNIX_TIMESTAMP().

Epoch 값은 본질적으로 UTC이므로 저장에 널리 사용되는 이유 중 하나가 됩니다.

SELECT
  TO_TIMESTAMP(1709294400)               AS as_ts,   -- from epoch
  EXTRACT(EPOCH FROM NOW())::bigint        AS as_epoch; -- to epoch

데이터베이스별 시간대 참고 사항

어떤 환경에서도 능숙하게 설명할 수 있도록 빠르게 정리하면 다음과 같습니다:

Postgres: timestamptz와 AT TIME ZONE을 사용하며, 지원이 가장 풍부합니다.
MySQL: TIMESTAMP는 세션의 time_zone을 통해 자동으로 변환되고, CONVERT_TZ(t, from, to)는 명시적으로 변환합니다. DATETIME은 시간대를 인식하지 않습니다.
SQL Server: datetimeoffset은 오프셋을 저장하고, AT TIME ZONE 'name'은 Windows 시간대 이름을 사용하여 변환합니다.

-- MySQL explicit conversion
SELECT CONVERT_TZ(event_dt, 'UTC', 'Europe/Istanbul') AS local_dt
FROM events;

심화 예제: 자정을 넘기는 세션

미묘한 보고서 문제가 있습니다. 세션이 자정을 넘길 수 있을 때 현지 달력 날짜별로 세션 수를 세는 문제입니다. 해결책은 같은 원칙을 따르는 것입니다. 현지 시간으로 변환한 다음 구간화합니다.

시작과 종료를 timestamptz로 저장하고, 보고서에서는 변환한 시작 시각에서 현지 날짜를 도출합니다. 세션을 두 날짜에 걸쳐 나누어야 한다면 날짜 축과 조인해야 하며, 이는 후속 질문으로 제기하기 좋은 내용입니다.

SELECT
  DATE_TRUNC('day', started_at AT TIME ZONE 'Europe/Istanbul') AS local_day,
  COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY 1;

빠른 확인

권장하는 저장 전략과 그 이유를 확인해 보십시오.

복습: 시간대와 타임스탬프

기억해 두어야 할 원칙은 다음과 같습니다:

timestamptz로 UTC를 저장하고, 표시할 때만 이름이 있는 시간대로 변환합니다.
이름과 달리 timestamptz는 시간대가 아니라 UTC 시점을 저장합니다.
AT TIME ZONE은 입력 형식에 따라 양방향으로 작동합니다. timestamptz를 현지 벽시계 시간으로 변환하거나, 일반 timestamp를 어떤 시간대에 속한 값으로 해석합니다.
DST가 자동으로 적용되도록 지역 이름('America/New_York')을 사용하고 고정 오프셋은 피합니다.
날짜 단위로 자르기 전에 현지 시간으로 변환하고, 열은 명시적인 UTC 시점과 비교합니다.

자주 묻는 질문

“시간대와 타임스탬프” 강의는 무료인가요?

네 — “시간대와 타임스탬프” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 SQL Interview Prep 강의 전체를 잠금 해제할 수 있습니다. SQL Interview Prep 강의에는 총 4개의 강의가 포함되어 있습니다.

“시간대와 타임스탬프”에서 뭘 배우나요?

UTC를 저장하고 시간대를 변환하며, 면접관이 타임스탬프에 관해 자주 묻는 함정을 살펴봅니다. 브라우저에서 직접 실행하는 실습 코드로 SQL Interview Prep을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

SQL Interview Prep을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 SQL Interview Prep은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 4번째 강의입니다.

“시간대와 타임스탬프” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 SQL Interview Prep 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 SQL Interview Prep 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← SQL Interview Prep(으)로 돌아가기