DB에 날짜·시간 저장하기: SQLite TEXT vs INTEGER vs REAL 실측

시리즈: 날짜·시간·타임존 강좌 5/6 (고급). SQL은 이미 쓸 줄 알고, 이제 created_at 열에 무엇을 넣을지 정해야 하는 개발자를 위한 글입니다. 이 강좌를 마치면 시각 저장 형식을 실측한 근거로 고르고, 시간 데이터를 조용히 망가뜨리는 세 가지 버그를 알아보고, JSON API에서 오프셋을 정직하게 다룰 수 있습니다.

"DB 시간이 전부 9시간씩 밀려 있어요"라는 문의, "3월 10일 통계에 몇 건이 빠졌어요"라는 보고, "최신 주문이 목록 맨 위에 안 나와요"라는 버그. 대부분 DB에서 시간을 다루는 방식이 원인이고, 에러는 나지 않습니다. 쿼리는 돌고 행도 나오는데, 그 행이 틀렸을 뿐입니다. 이 강좌에서는 같은 100만 개의 시각을 SQLite에 세 가지 방식으로 저장해 크기와 속도를 재고, 그다음 일부러 망가뜨립니다. 오프셋 혼재, T와 공백, 존재하지 않는 날짜, C의 32비트 카운터, 그리고 서버 시간대에 따라 의미가 바뀌는 JSON입니다.

환경: SQLite 3.51.0(sqlite3 CLI와 Python sqlite3 모듈 모두 3.51.0), Python 3.9.6(시스템 tzdata 2026d), 경고 하나를 보기 위해서만 Python 3.12, Apple clang 21, JavaScriptCore jsc, Apple Silicon Mac (M1 Pro). 모든 명령에서 시간대를 명시했습니다(TZ=UTC, TZ=Asia/Seoul 등). 출력은 찍힌 그대로 붙였고 경로는 ~/demo/로 줄였습니다. SQL이 처음이라면 SQL 기초와 인덱스와 EXPLAIN QUERY PLAN부터 보세요.

SQLite에는 날짜 타입이 없다: 인스턴트를 저장하는 세 가지 방법

SQLite의 저장 클래스는 NULL, INTEGER, REAL, TEXT, BLOB 다섯 가지이고 날짜 타입은 없습니다. 날짜 함수가 받아들이는 시간 표현은 세 가지입니다.

• TEXT: 2026-03-10 09:00:00이나 2026-03-10T09:00:00Z 같은 ISO 8601.
• INTEGER: 유닉스 초(1970-01-01 00:00:00 UTC부터의 초). 'unixepoch' 수정자로 읽습니다.
• REAL: 율리우스일. 기원전 4714년 11월 24일 정오(역산 그레고리력)부터의 일수. 2026-03-01 00:00:00은 2461100.5입니다.

SELECT typeof('2026-03-10'), typeof(1773057600), typeof(julianday('2026-03-10'));
-- text|integer|real

재기 전에 설계의 대부분을 결정하는 구분 하나. 인스턴트는 전 세계 공통 시간축의 한 점에서 일어난 일(주문이 결제됨)입니다. 벽시계 시각은 어딘가의 벽에 걸린 시계가 가리키는 시각(마드리드 매장이 9시에 문을 엶)입니다. 인스턴트는 모두 UTC로 저장할 수 있습니다. 미래의 벽시계 시각은 그럴 수 없습니다. 그날 어떤 오프셋이 적용될지 아직 모르기 때문입니다. 이것은 강좌 6에서 다룹니다.

실측: 100만 행을 TEXT, INTEGER, REAL로

벤치마크 내용입니다. 2020~2029년의 무작위 시각 100만 개(random.Random(42))를 TEXT YYYY-MM-DD HH:MM:SS(UTC), INTEGER 유닉스 초, REAL 율리우스일로 각각 별도 SQLite 파일에 저장합니다. 그다음 열에 인덱스 하나를 만들고, 한 달 치 개수(ts >= ? AND ts < ?)를 인덱스가 있을 때와 없을 때 세고, 전체 행을 일별로 GROUP BY 합니다.

# bench.py (핵심) Python 3.9.6, sqlite 3.51.0
kinds = {
    "text":    ("TEXT",    lambda e: datetime.fromtimestamp(e, timezone.utc).strftime("%Y-%m-%d %H:%M:%S")),
    "integer": ("INTEGER", lambda e: e),
    "real":    ("REAL",    lambda e: e / 86400.0 + 2440587.5),   # 율리우스일
}
day_expr = {"text": "substr(ts,1,10)", "integer": "date(ts,'unixepoch')", "real": "date(ts)"}
# ... CREATE TABLE ev(id INTEGER PRIMARY KEY, ts <타입> NOT NULL); executemany; CREATE INDEX ev_ts ON ev(ts)

출력(ms는 최소/중앙값. 인덱스 조회 20회, 인덱스 없이 5회, GROUP BY 3회):

text     insert  2.62s  file  28.1 MB ->  56.2 MB with index | month count idx 0.16/0.17 ms (8503)  no idx 51.3/52.0 ms | group by day 428/431 ms (3653 days)
   plan: SEARCH ev USING COVERING INDEX ev_ts (ts>? AND ts<?)
integer  insert  0.50s  file  13.1 MB ->  26.1 MB with index | month count idx 0.15/0.15 ms (8503)  no idx 37.5/37.7 ms | group by day 423/424 ms (3653 days)
   plan: SEARCH ev USING COVERING INDEX ev_ts (ts>? AND ts<?)
real     insert  0.55s  file  17.1 MB ->  34.2 MB with index | month count idx 0.17/0.17 ms (8503)  no idx 43.8/44.0 ms | group by day 440/447 ms (3653 days)

의외였던 점 세 가지. 첫째, 인덱스가 있으면 범위 조회에서 타입 차이는 거의 사라집니다. 세 방식 모두 0.15~0.17 ms로, 전체 스캔보다 약 300배 빠릅니다. 둘째, TEXT 삽입 2.62초는 SQLite 탓이 아니었습니다. 값을 미리 만들어 두면 삽입은 TEXT 0.48초, INTEGER 0.44초, REAL 0.44초였고, 나머지 2초는 Python의 strftime이었습니다. 셋째, REAL은 1초 정밀도에서 아무것도 잃지 않았습니다. REAL과 INTEGER 파일을 id로 조인해 datetime(r.ts) <> datetime(n.ts,'unixepoch'), strftime('%f') 비교, unixepoch(r.ts) <> n.ts를 확인했더니 모두 0행이었습니다. TEXT의 진짜 비용은 크기입니다. 테이블과 인덱스 양쪽에서 INTEGER의 두 배가 넘습니다.

datetime('now')는 UTC: "9시간 밀림"의 정체

한국 팀에서 자주 나오는 문의가 "DB 시간이 전부 9시간 느려요"입니다. 느린 게 아닙니다. SQLite의 'now'는 항상 UTC이고, 프로세스의 시간대를 읽는 것은 'localtime' 수정자뿐입니다. 같은 명령을 네 시간대에서('now' 열은 실행한 순간의 UTC):

for tz in UTC Asia/Seoul America/New_York Europe/Madrid; do
  TZ=$tz sqlite3 :memory: "SELECT datetime('now'), datetime(1773057600,'unixepoch'),
                                  datetime(1773057600,'unixepoch','localtime');"
done

UTC                2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 12:00:00
Asia/Seoul         2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 21:00:00
America/New_York   2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 08:00:00
Europe/Madrid      2026-11-15 10:13:33|2026-03-09 12:00:00|2026-03-09 13:00:00

Asia/Seoul에서 12:00 UTC는 21:00이고 차이는 정확히 9시간입니다. 로그가 "9시간 밀렸다"고 느껴지면 먼저 UTC로 기록되고 있는 것뿐인지 의심하세요. 뉴욕이 07:00이 아니라 08:00인 이유는 2026년 3월 8일에 미국 서머타임이 시작됐기 때문입니다(강좌 2). 지금의 한국은 서머타임이 없어서, 이 1시간 차이는 해외 사용자나 해외 리전 서버를 다룰 때 처음 만나게 됩니다.

규칙은 두 가지입니다. 저장은 datetime('now') 그대로 하고 변환은 화면에 보여 줄 때 한다. 그리고 저장하는 데이터에 'localtime'을 쓰지 않는다. SQLite도 이것을 일부 강제합니다. datetime(at,'localtime') 식 인덱스는 인덱스에 행이 들어가는 순간 실패합니다(CREATE INDEX 전에 넣은 행이든 후에 넣은 행이든 같았습니다).

Error: stepping, non-deterministic use of datetime() in an index

unixepoch(at) 식 인덱스는 문제없고, EXPLAIN QUERY PLAN은 SEARCH ev USING COVERING INDEX ev_at_epoch (<expr>>? AND <expr><?)를 보여 줬습니다.

음력 생일처럼 양력이 아닌 날짜를 다뤄야 할 때도 같은 원칙입니다. 원본 입력(음력 월·일과 윤달 여부)을 그대로 저장하고, 해마다 바뀌는 양력 날짜는 계산 결과로 따로 둡니다. 계산된 양력 날짜만 저장하면 다음 해에 쓸 수 없습니다.

흔한 실수: 오프셋이 섞인 ISO 문자열

ISO 8601 문자열이 올바르게 정렬되는 것은 모든 행이 같은 형식, 같은 오프셋일 때뿐입니다. 클라이언트마다 자기 현지 시각을 보내고 서버가 그 문자열을 그대로 저장하면 이 전제가 깨집니다.

CREATE TABLE b(id INTEGER PRIMARY KEY, at TEXT);
INSERT INTO b(at) VALUES
 ('2026-03-10T09:00:00+09:00'),   -- 서울·도쿄, UTC 00:00
 ('2026-03-09T20:00:00-05:00'),   -- 뉴욕, 10일 UTC 01:00
 ('2026-03-10T01:30:00+01:00'),   -- 마드리드, UTC 00:30
 ('2026-03-09T23:45:00Z');        -- UTC
SELECT id, at FROM b ORDER BY at;
SELECT id, at, datetime(at), unixepoch(at) FROM b ORDER BY unixepoch(at);

text order:
2|2026-03-09T20:00:00-05:00
4|2026-03-09T23:45:00Z
3|2026-03-10T01:30:00+01:00
1|2026-03-10T09:00:00+09:00
instant order:
4|2026-03-09T23:45:00Z|2026-03-09 23:45:00|1773099900
1|2026-03-10T09:00:00+09:00|2026-03-10 00:00:00|1773100800
3|2026-03-10T01:30:00+01:00|2026-03-10 00:30:00|1773102600
2|2026-03-09T20:00:00-05:00|2026-03-10 01:00:00|1773104400

문자열 순서는 2, 4, 3, 1. 실제 순서는 4, 1, 3, 2. 가장 늦게 일어난 뉴욕 행이 맨 앞에 옵니다. 건수가 늘면 더 심해집니다. 2026년 3월 한 달 동안 네 시간대의 오프셋을 무작위로 붙인 이벤트 10,000건(시드 42):

rows at the same position in both orders: 93
adjacent pairs out of order in text order: 3723 of 9999
'March 10 UTC' as text range: 331 rows, true: 335, overlap: 283

"3월 10일(UTC)" 문자열 범위 조회는 331행을 돌려줬지만 맞는 것은 283행뿐이었습니다. 다른 날의 48행이 섞여 들어오고 진짜 52행이 빠졌습니다. 합계는 그럴듯해 보여서 아무도 눈치채지 못합니다.

해결: INSERT 전에 UTC로 변환합니다(datetime(?)은 어떤 오프셋이든 UTC 문자열로 맞춰 줍니다. 또는 unixepoch(?)를 저장). 이미 오프셋이 섞인 데이터가 있다면 unixepoch(at)으로 정렬·필터링하고 그 식에 인덱스를 만듭니다.

같은 세션에서 찾은 문자열 함정 두 가지 더:

SELECT '2026-03-10 09:00:00' < '2026-03-10T08:00:00';   -- 1 (공백이 'T'보다 앞)
SELECT quote(datetime('2026-02-30 10:00:00'));          -- '2026-03-02 10:00:00'
SELECT quote(date('2026-02-29'));                       -- '2026-03-01' (2026년은 윤년이 아님)
SELECT quote(datetime('2026-13-01')), quote(datetime('2026/03/10 10:00'));  -- NULL|NULL

첫 줄은 직접 만든 T 구분 문자열과 datetime()이 돌려주는 공백 구분 문자열을 섞을 때의 함정입니다. 9시가 8시보다 "이전"으로 판정됩니다. 둘째 줄은 조용한 정규화입니다. 2월 30일이 에러가 아니라 3월 2일이 됩니다. 강좌 3의 PHP에서 본 넘침과 같습니다. 셋째, 모르는 형식은 NULL이 되므로 NOT NULL 제약이 값싼 방어선이 됩니다.

드라이버에도 버릇이 있다: Python sqlite3

Python의 sqlite3는 datetime을 자동으로 변환할 수 있습니다. detect_types=PARSE_DECLTYPES로 열을 TIMESTAMP로 선언하면 naive 값과 aware 값 모두 저장은 됐지만, 읽을 때 aware 행에서 실패했습니다.

3.9.6
[('2026-03-10 09:00:00',), ('2026-03-10 09:00:00+09:00',)]
ValueError: invalid literal for int() with base 10: b'00+09'

Python 3.12에서는 같은 스크립트가 먼저 아래 경고를 내고, 그다음 같은 ValueError로 멈춥니다.

~/demo/adapt.py:8: DeprecationWarning: The default datetime adapter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes
~/demo/adapt.py:12: DeprecationWarning: The default timestamp converter is deprecated as of Python 3.12; see the sqlite3 documentation for suggested replacement recipes

변환은 직접 하세요. 넣을 때 int(dt.timestamp()), 꺼낼 때 datetime.fromtimestamp(x, timezone.utc). 그러면 어느 Python 버전에서도 같은 동작이 됩니다.

MySQL DATETIME과 TIMESTAMP, 다른 DB (여기서는 실행하지 않음)

이 강좌에서는 MySQL이나 PostgreSQL 서버를 띄우지 않았습니다. 아래는 문서에 나온 동작입니다.

호스팅 업체가 정한 time_zone을 바꿀 수 없는 공유 호스팅에서 TIMESTAMP 열을 쓰면 "이전했더니 시간이 바뀌었다"가 생기기 쉽습니다. 접속 직후 SET time_zone = '+00:00'을 실행하면 변환이 아무 일도 하지 않게 됩니다. 공유 호스팅에서 MySQL의 다른 제한은 공유 호스팅의 max_connections_per_hour를 참고하세요.

2038년 문제: C의 32비트 카운터로 실측

부호 있는 32비트 정수의 유닉스 초는 2,147,483,647, 즉 2038-01-19 03:14:07 UTC에서 끝납니다. 이 Mac의 time_t는 64비트라서 int32_t로 32비트 상황을 만듭니다.

/* y2k38.c */
#include <inttypes.h>
#include <stdint.h>
#include <stdio.h>
#include <time.h>

static void show(const char *label, time_t t) {
    char buf[32];
    struct tm tm;
    gmtime_r(&t, &tm);
    strftime(buf, sizeof buf, "%Y-%m-%d %H:%M:%S", &tm);
    printf("%-22s %12lld  %s UTC\n", label, (long long)t, buf);
}

int main(void) {
    printf("sizeof(time_t) = %zu bytes\n", sizeof(time_t));
    int32_t last = INT32_MAX;                         /* 2147483647 */
    show("int32 max", last);
    show("time_t max+1 (64-bit)", (time_t)last + 1);
    int32_t wrapped = (int32_t)((uint32_t)last + 1u); /* 32비트 카운터의 동작 */
    show("int32 after +1 (wrap)", wrapped);
    int32_t ub = last;
    ub = ub + 1;                                      /* 부호 있는 오버플로: 정의되지 않은 동작 */
    show("int32 + 1 (UB)", ub);
    return 0;
}

$ clang -std=c17 -Wall -Wextra -O0 y2k38.c -o y2k38 && ./y2k38
sizeof(time_t) = 8 bytes
int32 max                2147483647  2038-01-19 03:14:07 UTC
time_t max+1 (64-bit)    2147483648  2038-01-19 03:14:08 UTC
int32 after +1 (wrap)   -2147483648  1901-12-13 20:45:52 UTC
int32 + 1 (UB)          -2147483648  1901-12-13 20:45:52 UTC

2038-01-19 03:14:07의 1초 뒤, 32비트 카운터는 1901-12-13 20:45:52로 돌아갑니다. ub + 1에 컴파일러 경고는 없었고, 실행 중에 UBSan(-fsanitize=undefined)만 잡았습니다.

y2k38.c:22:13: runtime error: signed integer overflow: 2147483647 + 1 cannot be represented in type 'int'

왜 "돌아간다"가 아니라 "정의되지 않음"인지는 정의되지 않은 동작과 최적화에서 다룹니다. 2026년에도 32비트 시간이 숨어 있는 곳은 4바이트 시간 필드를 가진 바이너리 형식과 통신 프로토콜, MySQL TIMESTAMP, 오래된 임베디드 펌웨어, 시간을 32비트 int에 담는 코드입니다. Python의 struct는 아예 거부합니다: struct.error: 'i' format requires -2147483648 <= number <= 2147483647. SQLite에는 이 한계가 없어서 datetime(2147483648,'unixepoch')는 2038-01-19 03:14:08을 돌려줬습니다.

JSON API: 오프셋이 있는 ISO 8601로 보내고, 받을 때는 의심하라

JSON에는 날짜 타입이 없어서 API마다 약속이 필요합니다. 시간대가 바뀌어도 의미가 유지되는 것은 오프셋을 명시한 ISO 8601 / RFC 3339, 가능하면 Z가 붙은 UTC입니다. 도구별 실제 동작:

# Python 3.9.6
TypeError: Object of type datetime is not JSON serializable
{"at": "2026-03-10T09:00:00+09:00"} {"at": "2026-03-10T00:00:00Z"}
ValueError: Invalid isoformat string: '2026-03-10T00:00:00Z'      # 3.9의 fromisoformat
2026-03-10 00:00:00+00:00                                          # 3.12에서 같은 호출

json.dumps는 datetime을 거부합니다. 형식을 직접 고르게 되니 오히려 좋습니다. Python 3.9의 fromisoformat은 JavaScript가 만드는 Z를 읽지 못합니다(3.11에서 지원, 3.12에서 동작 확인). JavaScript를 두 시간대에서:

const d = new Date("2026-03-10T09:00:00+09:00");
print(JSON.stringify({ at: d }));
print(JSON.stringify({ at: new Date("2026-03-10T09:00:00") }));   // 오프셋 없음!
print(JSON.stringify({ at: new Date("not a date") }));

TZ=UTC
{"at":"2026-03-10T00:00:00.000Z"}
{"at":"2026-03-10T09:00:00.000Z"}
{"at":null}
TZ=Asia/Seoul
{"at":"2026-03-10T00:00:00.000Z"}
{"at":"2026-03-10T00:00:00.000Z"}
{"at":null}

오프셋이 있는 문자열은 어디서나 같은 순간이었습니다. 오프셋이 없는 문자열은 현지 시각으로 읽혀서, 같은 코드가 서버 시간대에 따라 9시간 다른 순간을 보냈습니다. 개발 PC는 한국 시간, 운영 서버는 UTC인 흔한 구성에서 정확히 이 차이가 납니다. 잘못된 날짜는 예외 없이 null이 되니 직렬화 전에 검증하세요. HTTP 헤더는 또 다른 약속(Date: ... GMT)을 따르며 HTTP 캐싱에서 다룹니다.

API 계약 규칙:

• 인스턴트: "2026-03-10T00:00:00Z", 또는 이름으로 단위를 알 수 있는 필드의 에포크 초(created_at_unix). 단위 없는 에포크 숫자는 쓰지 않습니다. 밀리초를 초로 읽으면 어떻게 되는지는 강좌 1에서 봤습니다.
• 시간 없는 날짜(생일, 마감일): "2026-03-10". 시간대도 자정도 붙이지 않습니다.
• 미래의 현지 일정: 현지 시각과 IANA 존 이름. {"local": "2026-03-10T09:00", "zone": "Asia/Seoul"}.
• 오프셋 없는 문자열은 API 입구에서 추측하지 말고 거부합니다.

솔직한 한계와 버전 메모

• 측정은 Apple Silicon Mac (M1 Pro)의 SQLite 3.51.0에서만, 벤치마크 스크립트 1회 실행입니다. 절댓값은 환경마다 다르지만 비율(크기 2배, 인덱스 속도 동일)은 바뀌지 않을 것입니다. unixepoch()는 SQLite 3.38.0, 'subsec' 수정자는 3.42.0에서 추가됐습니다. 오래된 SQLite에서는 strftime('%s', ...)를 쓰세요.
• MySQL과 PostgreSQL 동작은 문서 기준 설명이고 여기서 실행하지 않았습니다.
• Python sqlite3의 기본 어댑터는 3.12부터 사용 중단 예정입니다(위 경고). datetime.fromisoformat은 3.11부터 Z를 받습니다.
• 이 macOS의 time_t는 64비트이고 32비트 상황은 int32_t로 만들었습니다. 32비트 리눅스의 64비트 time_t 전환 시기는 환경마다 다르며 여기서는 실행하지 않았습니다.
• REAL 율리우스일은 100만 행에서 1초 정밀도 왕복이 완전히 일치했습니다. 마이크로초 데이터는 시험하지 않았습니다. 마이크로초가 필요하면 INTEGER 마이크로초가 안전합니다.

연습 문제

1. orders(created TEXT)에 2026-03-10T09:00:00+09:00과 2026-03-09 20:00:00(오프셋 없음. 뉴욕에서 돌던 예전 앱 서버가 씀)이 섞여 있습니다. 쿼리를 건드리기 전에 가장 먼저 할 두 가지는?

정답

1. 첫째, 각 형식이 무엇을 뜻하는지 확인합니다. 패턴별로 행 수를 세고(length(created), substr(created, 20)), 오프셋 없는 행을 어느 시간대가 썼는지 로그나 코드로 확인합니다. 둘째, INTEGER 새 열을 추가해 채웁니다. 오프셋이 있는 행은 unixepoch(created), 뉴욕 행은 애플리케이션에서 ZoneInfo("America/New_York")로 변환합니다(SQLite는 프로세스 시간대 이외의 이름 있는 시간대를 적용할 수 없습니다). 그다음에 읽기를 새 열로 바꾸고 인덱스를 만듭니다.

이전: 날짜·시간·타임존 강좌 4: 날짜 계산, 월말·윤년·기간 · 다음: 날짜·시간·타임존 강좌 6: 스케줄링, cron, 시간대, 시계 오차 · 프로그래밍 학습 로드맵으로 돌아가기