Guide

데이터베이스의 타임스탬프: 저장, 인덱싱 및 변환 가이드

이 페이지가 필요한 이유

타임스탬프는 이벤트 정렬, 범위 필터링, 로그 결합 등 거의 모든 쿼리를 구동합니다. 문제는 각 데이터베이스가 시간을 다르게 처리한다는 것입니다. 이 가이드는 빠른 의사결정 트리, 즉시 배송 가능한 테이블 스키마, MySQL, PostgreSQL 및 SQLite에 대한 변환을 제공하며, 데이터 디버깅이 필요할 때 전체 변환기에 대한 CTA도 제공합니다.

빠른 결정

항상 UTC로 저장하세요; 가장자리에서만 로컬로 포맷하세요.
정밀도: 분석에는 몇 초면 충분합니다. 로그/추적의 경우 밀리초(또는 그 이상)입니다.
유형: 가능한 경우 시간대 인식 유형을 사용합니다. 상호 운용성이 중요한 경우 'BIGINT' 시대로 되돌아갑니다.
인덱스: 함수에서 열을 래핑하지 마세요. 필요한 경우 파생 열을 미리 계산합니다.
보존: 인덱스가 팽창하기 전에 오래된 데이터를 분할하거나 정리합니다.

엔진별 추천타입

마이SQL

선호: TIMESTAMP(범위 1970-2038) 또는 DATETIME(더 넓은 범위)이 UTC로 저장됩니다.
더 높은 정밀도 또는 2038년 이후 날짜의 경우: 'BIGINT' 에포크(밀리초 또는 마이크로초).
표 스니펫:

CREATE TABLE events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  occurred_at_ms BIGINT GENERATED ALWAYS AS (UNIX_TIMESTAMP(occurred_at) * 1000) STORED,
  INDEX idx_events_occurred_at (occurred_at)
);

포스트그레SQL

선호: TIMESTAMPTZ(UTC를 저장하고 시간대를 사용하여 렌더링).
정밀도: 최대 마이크로초 시대에 대한 GENERATED ALWAYS 열을 지원합니다.

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  occurred_at_ms BIGINT GENERATED ALWAYS AS (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT STORED,
  INDEX (occurred_at)
);

SQLite

TEXT, REAL 또는 INTEGER로 저장합니다. 일관성을 위해 'INTEGER' 에포크를 선택하세요.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  occurred_at_ms INTEGER NOT NULL, -- store UTC milliseconds
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_events_occurred_at_ms ON events (occurred_at_ms);

시대 ⇔ 인간 시간 변환

마이SQL

-- epoch (seconds) -> timestamp
SELECT FROM_UNIXTIME(1704067200) AS utc_time;
-- timestamp -> epoch seconds/milliseconds
SELECT UNIX_TIMESTAMP(occurred_at) AS epoch_s,
       UNIX_TIMESTAMP(occurred_at) * 1000 AS epoch_ms
FROM events;
-- apply timezone safely
SELECT CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') FROM events;

포스트그레SQL

-- epoch seconds -> timestamptz (UTC)
SELECT to_timestamp(1704067200) AT TIME ZONE 'UTC';
-- timestamptz -> epoch seconds/milliseconds
SELECT EXTRACT(EPOCH FROM occurred_at) AS epoch_s,
       (EXTRACT(EPOCH FROM occurred_at) * 1000)::BIGINT AS epoch_ms
FROM events;
-- render in a specific zone
SELECT occurred_at AT TIME ZONE 'America/New_York' FROM events;

SQLite

-- epoch milliseconds -> UTC datetime string
SELECT datetime(occurred_at_ms / 1000, 'unixepoch') AS utc_time FROM events;
-- UTC datetime string -> epoch milliseconds
SELECT strftime('%s', '2024-12-01 00:00:00') * 1000 AS epoch_ms;

테스트하는 동안 정확한 변환기가 필요합니까? Unix 타임스탬프 변환기 또는 **배치 타임스탬프 변환기**로 이동하세요.

인덱싱 및 쿼리 패턴

범위 필터(WHERE 발생_at >= ... AND 발생_at < ...)를 사용하여 sargable을 유지하세요.
필터에서 DATE(), CAST() 또는 ::date로 열을 래핑하지 마세요. 일별 그룹화가 필요한 경우 계산/생성 열을 사용하세요.
핫 파티션의 경우 두 가지를 모두 기준으로 필터링할 때 복합 인덱스 (occurred_at, user_id)를 고려하세요.
파티셔닝/보존: 최근 파티션을 작게 유지합니다. 오래된 파티션을 더 저렴한 스토리지에 보관하세요.
순서: ORDER BY 발생_at DESC LIMIT 100은 선행 열이 occurred_at인 경우 색인 친화적입니다.

일반적인 함정(및 수정 사항)

2038년 한도(MySQL TIMESTAMP): 미래 날짜 행에는 DATETIME(3) 또는 에포크 BIGINT를 사용합니다.
세션 시간대 드리프트: 연결 시작 시 time_zone='+00:00'(MySQL) 또는 SET TIMEZONE='UTC';(PostgreSQL)를 설정합니다.
혼합 정밀도: 같은 열에 초와 밀리초를 저장하지 마세요. 섭취 시 길이를 적용합니다.
함수 래핑 필터: WHERE DATE(occurred_at)=...는 인덱스를 비활성화합니다. 대신 occurred_at_date를 미리 계산하세요.
보고서의 DST 놀라움: UTC를 저장합니다. 실제 IANA 영역을 사용하여 뷰 계층에서 지역화합니다.

마이그레이션 체크리스트

쓰기를 중지하거나 이중 쓰기 레이어를 통해 라우팅합니다.
새 UTC 열을 추가합니다(예: 'occurred_at_utc TIMESTAMPTZ' 또는 'occurred_at_ms BIGINT').
결정적 변환을 통한 백필 현장 점검으로 확인하세요.
새 열에 인덱스를 추가한 다음 해당 열로 읽기를 전환합니다.
모니터링 및 백필 확인 후 기존 열을 제거합니다.

FAQ

에포크(epoch)로 저장해야 하나요, 날짜/시간으로 저장해야 하나요? 에포크 BIGINT는 상호 운용성과 정확성에 적합합니다. 'TIMESTAMPTZ'는 내장된 날짜 계산 및 가독성 측면에서 더 안전합니다.
어떤 정밀도를 선택해야 합니까? 로그/추적: ms 이상. 비즈니스 데이터/분석: 일반적으로 몇 초이면 충분합니다.
사용자가 현지 시간을 보내는 것을 어떻게 중지합니까? 가장자리에서 정규화: 시간대가 포함된 입력을 수락하고 UTC로 변환하고 UTC만 저장합니다.
인바운드 타임스탬프를 어떻게 검증합니까? 숫자 전용 페이로드, 길이 기반 정밀 감지 및 합리적인 범위(예: 2000-01-01 ~ 2100-01-01)를 시행합니다. **타임스탬프 정밀도 수준**을 참조하세요.