데이터베이스의 유닉스 타임스탬프 — 저장 방식, 쿼리, 모범 사례

대부분의 데이터베이스 스키마는 날짜와 시간을 저장해야 합니다. 선택지는 보통 두 가지입니다. 유닉스 타임스탬프(1970년 이후 경과 초를 나타내는 정수)로 저장하거나, DB가 제공하는 네이티브 datetime 컬럼 타입을 사용하는 방식이죠. 둘 다 가능합니다. 차이는 데이터를 어떻게 조회/정렬/비교/표시하느냐, 그리고 시간대(timezone)를 다루면서 얼마나 고생하느냐에서 나타납니다.

유닉스 타임스탬프 변환기는 개별 값 변환을 바로 해줍니다. 이 글에서는 데이터베이스 레벨에서의 트레이드오프와 주요 DB별 동작 차이를 다룹니다.

정수 vs 네이티브 datetime 컬럼: 핵심 트레이드오프

정수(유닉스 타임스탬프) 저장:

  • 컬럼 타입: INT 또는 BIGINT
  • 저장 내용: 1970-01-01 00:00:00 UTC 이후의 초(또는 밀리초) 수
  • 시간대: 없음 — 정수 값은 항상 UTC 기준
  • 산술: 매우 쉬움(예: “지난 24시간” = created_at > NOW_UNIX - 86400)
  • 가독성: 사람이 이해하려면 변환이 필요(1714000000이 언제인지 바로 알기 어려움)

네이티브 datetime 저장:

  • 컬럼 타입: DB에 따라 TIMESTAMP, TIMESTAMPTZ, DATETIME
  • 저장 내용: 구조화된 날짜/시간 표현
  • 시간대: 타입/DB에 따라 다름(아래 참고)
  • 산술: DB별 함수가 필요(DATEDIFF, EXTRACT, interval 연산 등)
  • 가독성: 쿼리 결과에서 사람이 읽기 쉬움(2024-04-25 11:46:40)

정답은 하나가 아닙니다. 사용 사례, 쿼리 패턴, 앱에서 시간대를 처리하는 방식에 따라 달라집니다.

PostgreSQL: TIMESTAMP vs TIMESTAMPTZ vs 정수

PostgreSQL에서는 실무적으로 세 가지 선택지가 있습니다.

TIMESTAMP WITHOUT TIME ZONE — 입력한 그대로 저장합니다. 시간대 변환이 없습니다. 예를 들어 뉴욕에 있는 애플리케이션 서버에서 '2024-04-25 09:00:00'을 넣으면 그대로 저장됩니다(실제로는 UTC 13:00을 의미하는 순간일 수 있음). 읽어올 때도 시간대 정보 없이 2024-04-25 09:00:00이 반환됩니다. 서버 시간대가 바뀌거나 여러 시간대가 섞이는 시스템에서는 위험합니다.

TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) — 내부적으로 항상 UTC로 저장합니다. 저장 시 세션 시간대에서 UTC로 변환하고, 조회 시 UTC에서 세션 시간대로 다시 변환합니다. 대부분의 운영 환경에서 권장되는 타입입니다. 이름이 오해를 부르지만, “시간대를 저장”하는 게 아니라 “UTC로 저장하고 변환”합니다.

-- Session timezone: America/New_York (UTC-4)
INSERT INTO events (created_at) VALUES ('2024-04-25 09:00:00');
-- Stored as: 2024-04-25 13:00:00 UTC

SELECT created_at FROM events;
-- Returns: 2024-04-25 09:00:00-04 (converted back to session timezone)

BIGINT (유닉스 타임스탬프) — 시간대와 완전히 무관합니다. 변환은 애플리케이션이 책임집니다. PostgreSQL의 시간대 처리 자체를 우회하고 앱 레이어에서 전부 통제하고 싶을 때 유용합니다.

-- Range query (last 7 days)
SELECT * FROM events WHERE created_at > EXTRACT(EPOCH FROM NOW())::bigint - 604800;

-- Convert to readable date
SELECT TO_TIMESTAMP(created_at) FROM events;

대부분의 신규 PostgreSQL 애플리케이션에는 TIMESTAMPTZ를 추천합니다. 시간대 처리도 올바르고, 타입 자체가 읽기 쉬우며, PostgreSQL의 날짜/시간 함수들을 바로 활용할 수 있습니다.

MySQL: DATETIME vs TIMESTAMP vs INT

MySQL은 이름이 헷갈립니다. MySQL의 TIMESTAMP는 PostgreSQL의 TIMESTAMPTZ와 이름은 비슷해도 구현/제약이 다릅니다.

DATETIME — 입력한 날짜/시간을 그대로 저장합니다. 시간대 변환이 없습니다. PostgreSQL의 TIMESTAMP WITHOUT TIME ZONE과 유사합니다. 범위: 1000-01-01 ~ 9999-12-31.

TIMESTAMP — 내부적으로 UTC로 저장하고, 읽기/쓰기 때 연결(connection) 시간대 기준으로 변환합니다. 동작은 PostgreSQL의 TIMESTAMPTZ와 유사합니다. 다만 범위가 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC로 제한됩니다. 이것이 2038년 문제입니다. MySQL의 TIMESTAMP 컬럼 타입은 내부적으로 32비트 정수를 쓰기 때문에 오버플로우가 발생합니다.

INT 또는 BIGINT — 유닉스 타임스탬프를 수동으로 관리합니다. BIGINT를 쓰면 2038년 문제를 피할 수 있습니다(2038 이후도 저장 가능).

-- 2038 problem with TIMESTAMP column
-- This will fail or be stored incorrectly:
INSERT INTO events (created_at) VALUES (FROM_UNIXTIME(2147483648)); -- Jan 19, 2038 + 1 second

-- INT/BIGINT approach avoids this
INSERT INTO events (created_at) VALUES (2147483648); -- stores fine
SELECT FROM_UNIXTIME(created_at) FROM events; -- converts for display

2038년 이후까지 데이터를 보관해야 하는 MySQL 애플리케이션이라면 TIMESTAMP 대신 DATETIME 또는 BIGINT를 쓰는 편이 안전합니다.

SQLite: 사실상 텍스트/정수뿐

SQLite는 네이티브 datetime 타입이 없습니다. 날짜를 다음 형태로 저장합니다.

  • 텍스트(ISO 8601: '2024-04-25T11:46:40')
  • 정수(유닉스 타임스탬프)
  • 실수(율리우스 일수)

SQLite의 날짜 함수는 세 형태 모두를 다룰 수 있지만, 내부적으로는 결국 텍스트와 숫자입니다. 그래서 SQLite에서는 정수 타임스탬프 방식이 자연스럽고, 문자열 파싱 오버헤드를 피하면서 모호함도 줄일 수 있습니다.

-- Store as integer
CREATE TABLE events (created_at INTEGER);
INSERT INTO events (created_at) VALUES (strftime('%s', 'now'));

-- Query last 7 days
SELECT * FROM events WHERE created_at > strftime('%s', 'now') - 604800;

-- Display as human-readable
SELECT datetime(created_at, 'unixepoch', 'localtime') FROM events;

SQLite에서는 문자열 datetime 저장보다 정수 유닉스 타임스탬프가 깔끔한 선택인 경우가 많습니다.

자주 쓰는 쿼리: 정수 vs datetime 문법

작업Unix 정수(PostgreSQL)TIMESTAMPTZ(PostgreSQL)
지난 24시간 레코드WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 86400WHERE ts > NOW() - INTERVAL '24 hours'
지난 7일 레코드WHERE ts > EXTRACT(EPOCH FROM NOW())::bigint - 604800WHERE ts > NOW() - INTERVAL '7 days'
오늘부터 레코드WHERE ts >= date_trunc('day', NOW()) (먼저 변환 필요)WHERE ts >= date_trunc('day', NOW())
일 단위 그룹핑복잡함 — 변환 후 truncateGROUP BY date_trunc('day', ts)
사람이 읽기TO_TIMESTAMP(ts)ts (이미 읽기 쉬움)

정수 방식은 날짜를 자르는(trunc) 연산(일/주/월 단위 그룹핑 등)에서 명시적 변환이 더 많이 필요합니다. 네이티브 datetime 컬럼은 SQL에서 더 깔끔하게 처리됩니다.

어떤 것을 써야 하나: 선택 가이드

다음에 해당하면 TIMESTAMPTZ(PostgreSQL) 또는 DATETIME(MySQL)을 권장합니다.

  • 팀이 SQL을 직접 많이 작성한다(쿼리 가독성이 중요)
  • DB 쿼리에서 일/주/월 단위로 그룹핑해야 한다
  • 변환 오버헤드 없이 DB 내장 시간 함수를 쓰고 싶다

다음에 해당하면 유닉스 정수 타임스탬프가 적합할 수 있습니다.

  • MySQL을 쓰는데 2038년 이후 데이터가 필요하다
  • 시간대 로직을 앱 레이어에서 전부 처리하고, DB의 시간대 변환을 우회하고 싶다
  • SQLite를 사용한다
  • JavaScript 프런트엔드로 타임스탬프를 전달하는 API를 만든다(초/밀리초 스케일만 문서화하면 정수는 모호하지 않음)
  • 매우 많은 타임스탬프를 저장하고, 더 컴팩트한 저장을 원한다(예: 4바이트 INT vs 가변적인 datetime 표현)

같은 애플리케이션 안에서 접근을 섞는 것은 피하세요. 어떤 테이블은 네이티브 datetime, 어떤 테이블은 정수 타임스탬프면, 모든 개발자에게 변환 부담과 맥락 전환 비용이 생깁니다.

무엇을 저장하든 문서화하세요. 컬럼 정의에 주석으로 “UTC Unix seconds”, “UTC Unix milliseconds”, “local datetime” 중 무엇인지 명확히 적어 두는 것이 좋습니다. 디버깅이나 마이그레이션 중 특정 값을 확인할 때는 유닉스 타임스탬프 변환기로 검증할 수 있습니다.