DBに日時を保存する：SQLiteのTEXT・INTEGER・REALを実測比較

Q: SQLite で日時は TEXT と INTEGER のどちらで保存すべきですか？

起きた瞬間（インスタント）なら、UTC の 1 形式にそろえる限りどちらでも動きます。100 万件の実測では、索引を使った範囲検索はどちらも 0.15〜0.17 ms でしたが、索引込みのファイルは TEXT が 56.2 MB、INTEGER の Unix 秒が 26.1 MB でした。サイズと比較の確実さなら INTEGER、人がテーブルを直接読むなら UTC 固定形式の TEXT です。

Q: ISO 8601 の文字列列で ORDER BY すると順序がおかしいのはなぜですか？

文字列比較が時刻順と一致するのは、全行が同じ形式・同じオフセットのときだけです。+09:00、-05:00、+01:00、Z の 4 行を ORDER BY したところ、本当の順序とちょうど逆の並びになりました。保存前に UTC に変換するか、unixepoch(at) で並べてください。

Q: SQLite の datetime('now') は日本時間を返しますか？

返しません。TZ 環境変数に関係なく UTC です。UTC、Asia/Tokyo、America/New_York、Europe/Madrid の 4 つで実行して同じ値が出ました。プロセスのタイムゾーンを使うのは 'localtime' 修飾子だけです。

100 万件の時刻を SQLite に 3 方式で保存してサイズと索引速度を実測。datetime('now') は UTC、オフセット混在で順序が逆転する罠、MySQL の型、2038 年問題、JSON まで。

t.laboratory 公開日 2026年11月15日

シリーズ：日付・時刻・タイムゾーン講座 5/6（上級）。 SQL はもう書けて、created_at 列に何を入れるかを決める立場になった人向けです。この講座のあとは、時刻の保存形式を実測にもとづいて選び、時刻データを黙って壊す 3 つのバグを見抜き、JSON API でオフセットを正しく扱えるようになります。

「サーバーの時刻が 9 時間ずれている」という問い合わせ、「3 月 10 日の集計に何件か足りない」という報告、「最新の注文が一覧の先頭に来ない」という不具合。どれも DB の時刻の扱いが原因になりがちで、しかもエラーは出ません。クエリは走り、行も返り、ただその行が間違っています。この講座では同じ 100 万件の時刻を SQLite に 3 通りで保存してサイズと速度を測り、そのあとわざと壊します。オフセットの混在、T と空白、存在しない日付、C の 32 ビットカウンター、そしてサーバーのタイムゾーンで意味が変わる JSON です。

環境： SQLite 3.51.0（sqlite3 コマンドと 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/Tokyo など）。出力は表示どおりに貼り、パスは ~/demo/ に短縮しました。SQL 自体が初めてなら SQL の基本と索引と EXPLAIN QUERY PLAN から読んでください。

SQLite に日付型はない：インスタントを保存する 3 つの方法

SQLite の格納クラスは NULL、INTEGER、REAL、TEXT、BLOB の 5 つで、日付型はありません。日付関数が受け付ける時刻の表し方は 3 つです。

TEXT：2026-03-10 09:00:00 や 2026-03-10T09:00:00Z のような ISO 8601。
INTEGER：Unix 秒（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

測る前に、設計の大半を決める区別を 1 つ。インスタントは世界共通の時間軸上の 1 点で起きたこと（注文が支払われた）です。壁時計時刻（ウォールタイム）はどこかの壁の時計が指す時刻（マドリードの店が 9 時に開く）です。インスタントはすべて UTC で保存できます。未来の壁時計時刻はできません。その日にどのオフセットが適用されるかが、まだ決まっていないからです。これは講座 6 で扱います。

判断図。出発点は保存したい値。それは起きた瞬間（インスタント）か。はいなら INTEGER の Unix 秒（UTC）、人がテーブルを直接読むなら UTC の決まった 1 形式（末尾 Z）の TEXT。インスタントでないなら、誕生日のような日付だけか。そうなら TEXT の YYYY-MM-DD。それ以外は会議のような未来の現地時刻で、現地時刻と IANA のゾーン名を保存し、UTC は必要なときに計算する。 — SQLite で時刻の列を選ぶ。どの型が見やすいかではなく、その値が何を意味するかで決める。

実測：100 万件を TEXT・INTEGER・REAL で

ベンチマークの内容です。2020〜2029 年のランダムな時刻 100 万件（random.Random(42)）を、TEXT の YYYY-MM-DD HH:MM:SS（UTC）、INTEGER の Unix 秒、REAL のユリウス日として、それぞれ別の SQLite ファイルに保存します。そのあと列に索引を 1 つ張り、1 か月分の件数（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)

索引込みのデータベースファイルサイズの棒グラフ。TEXT の ISO 8601：56.2 MB。REAL のユリウス日：34.2 MB。INTEGER の Unix 秒：26.1 MB。テキストは整数の 2 倍以上。 — 同じ 1,000,000 件の UTC 時刻（2020〜2029 年、シード 42）を SQLite 3.51.0 に保存し、時刻列に索引を 1 つ張ったときのファイルサイズ。Apple Silicon Mac (M1 Pro)。索引を使った 1 か月分の件数取得は 3 方式とも 0.15〜0.17 ms。

	TEXT（19 文字、UTC）	INTEGER（Unix 秒）	REAL（ユリウス日）
索引込みファイル	56.2 MB	26.1 MB	34.2 MB
索引で 1 か月分を数える	0.16 ms	0.15 ms	0.17 ms
索引なし全件走査	51.3 ms	37.5 ms	43.8 ms
100 万件を日ごとに集計	428 ms	423 ms	440 ms
CLI でそのまま読める	はい	いいえ	いいえ
見つかった件数	8,503	8,503	8,503

意外だった点が 3 つあります。1 つ目、索引があれば範囲検索で型の差はほぼ消えます。3 方式とも 0.15〜0.17 ms で、全件走査の約 300 倍速い。2 つ目、TEXT の挿入 2.62 秒は SQLite のせいではありません。値を先に整形しておくと挿入は TEXT 0.48 秒、INTEGER 0.44 秒、REAL 0.44 秒で、残りの 2 秒は Python の strftime でした。3 つ目、REAL は 1 秒精度では何も失いませんでした。REAL と INTEGER のファイルを id で結合し、datetime(r.ts) <> datetime(n.ts,'unixepoch')、strftime('%f') の比較、unixepoch(r.ts) <> n.ts のどれも 0 件でした。TEXT の本当のコストはサイズで、表と索引の両方で INTEGER の 2 倍以上になります。

datetime('now') は UTC。「9 時間ずれている」の正体

日本のチームでよくある問い合わせが「DB の時刻が全部 9 時間ずれている」です。ずれてはいません。SQLite の 'now' は常に UTC で、プロセスのタイムゾーンを読むのは 'localtime' 修飾子だけです。同じコマンドを 4 つのゾーンで（'now' の列は実行した瞬間の UTC）：

for tz in UTC Asia/Tokyo 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:03:42|2026-03-09 12:00:00|2026-03-09 12:00:00
Asia/Tokyo         2026-11-15 10:03:42|2026-03-09 12:00:00|2026-03-09 21:00:00
America/New_York   2026-11-15 10:03:42|2026-03-09 12:00:00|2026-03-09 08:00:00
Europe/Madrid      2026-11-15 10:03:42|2026-03-09 12:00:00|2026-03-09 13:00:00

Asia/Tokyo では 12:00 UTC が 21:00 になり、差はちょうど 9 時間です。ログを見て「9 時間ずれている」と感じたら、まず UTC で記録されているだけではないかを疑ってください。ニューヨークが 07:00 ではなく 08:00 なのは、2026 年 3 月 8 日に米国の夏時間が始まったからです（講座 2）。日本は夏時間がないので、この 1 時間の差は海外ユーザーや海外リージョンのサーバーを扱って初めて出会うことになります。

ルールは 2 つ。保存は 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><?) を返しました。

和暦（令和 8 年など）を画面に出したい場合も同じ考え方です。DB には西暦の UTC を保存し、和暦への変換は表示層で行います。改元のたびに DB を書き換える設計は避けてください（和暦の表示は講座 3 で扱っています）。

よくある間違い：オフセットが混ざった 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 月の 1 か月に 4 つのゾーンのオフセットをランダムに付けたイベント 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) で並べ替え・絞り込みをし、その式に索引を張ります。

同じセッションで見つけた文字列の罠をあと 2 つ：

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

1 行目は、自分で作った T 区切りの文字列と、datetime() が返す空白区切りを混ぜたときの罠です。9 時が 8 時より「前」と判定されます。2 行目は黙って正規化される例で、2 月 30 日がエラーにならず 3 月 2 日になります。講座 3 の PHP で見たあふれと同じです。3 行目、知らない形式は 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 のサーバーは動かしていません。以下はドキュメントにある挙動です。

	MySQL `DATETIME`	MySQL `TIMESTAMP`	PostgreSQL `timestamptz`	SQLite
保存されるもの	渡した壁時計時刻、ゾーンなし	インスタント（UTC で保持）	インスタント（UTC で保持）	入れたものそのまま
読み書き時の変換	しない	セッションの `time_zone` で変換	セッションの `TimeZone` で変換	しない
範囲	1000〜9999 年	2038-01-19 03:14:07 UTC まで	非常に広い	形式しだい
よくある驚き	ゾーンの違うアプリサーバーが食い違う値を書く	`time_zone` を変えると表示が全部ずれる	出力にセッションゾーンのオフセットが付く	TEXT のオフセット混在

共用レンタルサーバーで time_zone を自分で設定できず、列が TIMESTAMP だと「移行したら時刻がずれた」が起きやすくなります。接続直後に SET time_zone = '+00:00' を実行すれば変換は何もしなくなります。共用サーバーでの MySQL のほかの制限は共用サーバーの max_connections_per_hour を参照してください。

2038 年問題：C の 32 ビットカウンターで実測

符号付き 32 ビット整数の Unix 秒は 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 を 2 つのゾーンで：

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/Tokyo
{"at":"2026-03-10T00:00:00.000Z"}
{"at":"2026-03-10T00:00:00.000Z"}
{"at":null}

オフセット付きの文字列はどこでも同じ瞬間を指しました。オフセットなしの文字列は現地時刻として読まれ、同じコードがサーバーのゾーンによって 9 時間違う瞬間を送りました。開発機が日本時間で本番が UTC、というよくある構成で、まさにこの差が出ます。不正な日付は例外にならず null になるので、シリアライズ前に検証してください。HTTP ヘッダーはさらに別の決め事（Date: ... GMT）で、HTTP キャッシュで扱っています。

API 契約のルール：

インスタント："2026-03-10T00:00:00Z"、または名前で単位がわかるフィールドのエポック秒（created_at_unix）。単位のないエポック数値は使わない。ミリ秒を秒として読むとどうなるかは講座 1 で見ました。
時刻のない日付（誕生日、締め切り日）："2026-03-10"。ゾーンも 0 時も付けない。
未来の現地の予定：現地時刻と IANA ゾーン名。{"local": "2026-03-10T09:00", "zone": "Asia/Tokyo"}。
オフセットのない文字列は 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 ビット Linux の 64 ビット time_t 移行は環境ごとに時期が違い、ここでは試していません。
REAL のユリウス日は 100 万件で 1 秒精度の往復が完全に一致しました。マイクロ秒のデータは試していません。マイクロ秒が必要なら INTEGER のマイクロ秒が安全です。

演習

orders(created TEXT) に 2026-03-10T09:00:00+09:00 と 2026-03-09 20:00:00（オフセットなし。ニューヨークで動いていた旧アプリサーバーが書いた）が混ざっています。クエリに手を付ける前に最初にやる 2 つのことは何ですか。

解答

1 つ目は、各形式が何を意味するかを確かめること。パターンごとに件数を数え（length(created)、substr(created, 20)）、オフセットのない行をどのゾーンが書いたかをログやコードで確認します。2 つ目は、INTEGER の新しい列を追加して埋めること。オフセット付きの行は unixepoch(created)、ニューヨークの行はアプリ側で ZoneInfo("America/New_York") を使って変換します（SQLite はプロセスのゾーン以外の名前付きゾーンを適用できません）。そのあとで読み出しを新しい列に切り替え、索引を張ります。

前へ： 日付・時刻・タイムゾーン講座 4：日付の計算、月末・うるう年・期間 · 次へ： 日付・時刻・タイムゾーン講座 6：スケジューリング、cron、タイムゾーン、時計のずれ · プログラミング学習ロードマップに戻る

よくある質問

SQLite で日時は TEXT と INTEGER のどちらで保存すべきですか？

起きた瞬間（インスタント）なら、UTC の 1 形式にそろえる限りどちらでも動きます。100 万件の実測では、索引を使った範囲検索はどちらも 0.15〜0.17 ms でしたが、索引込みのファイルは TEXT が 56.2 MB、INTEGER の Unix 秒が 26.1 MB でした。サイズと比較の確実さなら INTEGER、人がテーブルを直接読むなら UTC 固定形式の TEXT です。

ISO 8601 の文字列列で ORDER BY すると順序がおかしいのはなぜですか？