0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@my-agent-works(My Agent Works 合同会社)

SQLite で日時を比べるときは datetime() で形を揃える ― ISO 8601 と空白区切りが混ざった列を実験で確かめる

0
Posted at

はじめに

SQLite には、日付や時刻のための専用の型がありません。日時は TEXT・REAL・INTEGER のどれかで持ちます（SQLite: Datatypes の「Date and Time Datatype」）。

TEXT で持つ場合、書き込む側によって形が変わりがちです。

書き込む側 入る値の例
JavaScript の new Date().toISOString() 2026-09-10T23:00:00.000Z
SQL の datetime('now') 2026-09-10 23:00:00

どちらも「正しい日時」ですが、区切りが T と空白で違います。この2つが1つの列に混ざったまま、expires_at < ? のように文字列として比べると何が起きるかを、実験で確かめました。Cloudflare D1 のような SQLite 互換のデータベースでも、同じ考え方が当てはまります。

この記事の要点

  • SQLite には日時の型が無く、TEXT で持つと T 区切り（ISO 8601）と空白区切りが混ざりやすい
  • 形式が混ざった列を文字列のまま比べると、同じ日付の行だけ、相手の形式しだいで「消しすぎ」も「残しすぎ」も起きる
  • 両辺を datetime() で揃えれば正しく比べられる。読めない形式は NULL になり、比較から黙って外れる

環境

項目 内容
SQLite 3.50.4（Python 3.13.7 付属の sqlite3 モジュール）
実行 メモリ上のデータベース（:memory:）

コードの下に載せた出力は、読みやすいように見出しを付けて整えています（値はそのままです）。

1. 実験の準備

有効期限を持つ表に、形の違う3行を入れます。

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sessions (id TEXT PRIMARY KEY, expires_at TEXT)")
con.executemany("INSERT INTO sessions VALUES (?, ?)", [
    ("A_iso_live",    "2026-09-10T23:00:00.000Z"),  # ISO 8601・まだ有効
    ("B_space_live",  "2026-09-10 23:00:00"),       # 空白区切り・まだ有効
    ("C_iso_expired", "2026-09-10T01:00:00.000Z"),  # ISO 8601・切れている
])
NOW_ISO   = "2026-09-10T12:00:00.000Z"   # アプリ側で toISOString() を渡した場合
NOW_SPACE = "2026-09-10 12:00:00"        # SQL の datetime('now') と同じ形

いまを 12:00 とすると、「切れている」のは C だけです。

2. 文字列のまま比べる

q = "SELECT id FROM sessions WHERE {} ORDER BY id"
print([r[0] for r in con.execute(q.format("expires_at < ?"), (NOW_ISO,))])
print([r[0] for r in con.execute(q.format("expires_at < ?"), (NOW_SPACE,))])
print([r[0] for r in con.execute(q.format("datetime(expires_at) < datetime(?)"), (NOW_ISO,))])

相手がISO        : ['B_space_live', 'C_iso_expired']
相手が空白区切り  : []
datetime()で揃える: ['C_iso_expired']
  • 比べる相手が ISO 形式だと、まだ有効な B まで「切れた」と判定されます
  • 比べる相手が空白区切りだと、切れている C を「まだ有効」と判定します
  • 両辺を datetime() で揃えたときだけ、正しく C だけが選ばれます

期限切れの行を消す処理でこれが起きると、前者は生きている行を消し、後者は消すべき行を残し続けます。どちらもエラーは出ません。

3. なぜそうなるか

TEXT どうしの比較は、既定の照合順序（BINARY）で先頭から1バイトずつ比べます（SQLite: Collating Sequences）。同じ日付どうしなら、差が出るのは11文字目の区切り文字です。

2026-09-10 23:00:00        ← 空白  (0x20)
2026-09-10T12:00:00.000Z   ← T     (0x54)
          ^ ここで大小が決まる。0x20 < 0x54 なので、時刻に関係なく空白区切りのほうが「小さい」

日付が違えばその前で決着するので、同じ日付の行だけが時刻を無視して判定されます。毎回ではなく「たまに」間違うので、見つけにくい種類の誤りです。

4. datetime() で揃える

datetime() は、受け取った日時を YYYY-MM-DD HH:MM:SS の形に直して返します（SQLite: Date And Time Functions）。どんな値を受け付けるかを試しました。

for v in ["2026-09-10T23:00:00.000Z", "2026-09-10 23:00:00", "2026-09-10T23:00:00",
          "2026-09-10T23:00:00+09:00", "2026-09-10 23:00", "2026-09-10",
          "2026/09/10 23:00:00", "1788994800", "not a date"]:
    print(v, "->", con.execute("SELECT datetime(?)", (v,)).fetchone()[0])

datetime('2026-09-10T23:00:00.000Z')  -> '2026-09-10 23:00:00'
datetime('2026-09-10 23:00:00')       -> '2026-09-10 23:00:00'
datetime('2026-09-10T23:00:00')       -> '2026-09-10 23:00:00'
datetime('2026-09-10T23:00:00+09:00') -> '2026-09-10 14:00:00'
datetime('2026-09-10 23:00')          -> '2026-09-10 23:00:00'
datetime('2026-09-10')                -> '2026-09-10 00:00:00'
datetime('2026/09/10 23:00:00')       -> None
datetime('1788994800')                -> None
datetime('not a date')                -> None

読み取れることが3つあります。

  1. T 区切り・空白区切り・末尾の Z・秒の省略は、どれも同じ形に揃う
  2. +09:00 のような時差つきの値は、UTC に直されたうえで返る（23:00 → 14:00）
  3. スラッシュ区切りや Unix 秒は NULL になる。Unix 秒は、修飾子を付けないとユリウス日として読まれて範囲外になるため。数値の列でも文字列でも同じで、datetime(値, 'unixepoch') か datetime(値, 'auto') と書けば読める

3つ目には注意が要ります。NULL を含む比較は真にも偽にもならず、WHERE では「当てはまらない」として扱われます。読めない形式の行は、エラーにならずに黙って対象から外れます。形式の混在を疑うときは、先に SELECT count(*) FROM t WHERE datetime(col) IS NULL で読めない行を数えておくと安全です。

5. 小数秒は datetime() で落ちる

datetime() の結果は秒までです。同じ秒の中の前後は区別できません。

a, b = "2026-09-10T12:00:00.100Z", "2026-09-10T12:00:00.900Z"
print(con.execute("SELECT datetime(?) < datetime(?)", (a, b)).fetchone()[0])
print(con.execute("SELECT julianday(?) < julianday(?)", (a, b)).fetchone()[0])
print(con.execute("SELECT unixepoch(?, 'subsec') < unixepoch(?, 'subsec')", (a, b)).fetchone()[0])

datetime(a) < datetime(b)                    : 0
julianday(a) < julianday(b)                  : 1
unixepoch(a,'subsec') < unixepoch(b,'subsec'): 1

秒より細かい順序が要るなら、julianday()（実数）か unixepoch(値, 'subsec') で比べます。unixepoch() は SQLite 3.38.0 から、subsec 修飾子は 3.42.0 からの機能です（リリース履歴）。

6. 並び替えも同じように間違う

比較だけでなく、ORDER BY も文字列の順で並びます。

con.execute("CREATE TABLE ev (id TEXT, at TEXT)")
con.executemany("INSERT INTO ev VALUES (?, ?)", [
    ("x", "2026-09-10 12:30:00"), ("y", "2026-09-10T12:10:00.000Z"), ("z", "2026-09-10 12:20:00")])
print([r[0] for r in con.execute("SELECT id FROM ev ORDER BY at")])
print([r[0] for r in con.execute("SELECT id FROM ev ORDER BY datetime(at)")])

ORDER BY at           : ['z', 'x', 'y']
ORDER BY datetime(at) : ['y', 'z', 'x']

時刻順なら y（12:10）→ z（12:20）→ x（12:30）です。文字列のままだと、ISO 形式の y が最後に回ります。逆に「新しい順に10件」（ORDER BY at DESC）では、いちばん古い y が先頭に来ます。同じ日付なら区切りの T が空白より大きいので、ISO 形式の行は時刻に関係なく「新しい」側に並んでしまいます。

7. 数値で持つという選択

最初から Unix 秒（INTEGER）で持てば、形式の揺れは起きません。TEXT で持っている列も、比べるときに数値へ直せます。

for v in ["2026-09-10T23:00:00.000Z", "2026-09-10 23:00:00", "2026-09-10T23:00:00+09:00"]:
    print(v, con.execute("SELECT unixepoch(?), strftime('%s', ?)", (v, v)).fetchone())

unixepoch('2026-09-10T23:00:00.000Z')  -> 1789081200   strftime('%s') -> '1789081200'
unixepoch('2026-09-10 23:00:00')       -> 1789081200   strftime('%s') -> '1789081200'
unixepoch('2026-09-10T23:00:00+09:00') -> 1789048800   strftime('%s') -> '1789048800'

strftime('%s', …) は文字列を返すので、数値として比べるなら CAST(... AS INTEGER) を付けるか、unixepoch() を使います。

8. どう揃えるか

実際の運用では、次の順で考えると迷いません。

  1. **書く側の形を1つに決める。**JavaScript から書くなら toISOString() に揃える、SQL から書くなら datetime('now') に揃える、のどちらか
  2. **比べる側は、両辺を datetime()（または unixepoch()）で包む。**過去に入った行の形が揃っている保証は無いため
  3. 読めない行を数える。datetime(col) IS NULL の行は、比較から黙って外れている
-- 期限切れの行を消す（両辺を datetime() で揃える）
DELETE FROM sessions WHERE datetime(expires_at) < datetime('now');

-- 読めない形式の行が混ざっていないか
SELECT count(*) FROM sessions WHERE datetime(expires_at) IS NULL;

なお、WHERE datetime(expires_at) < … のように列を関数で包むと、expires_at の普通の索引は使われません。件数が多い表では、同じ式で式索引を作っておくと索引が使われます（SQLite: Indexes On Expressions）。

CREATE INDEX idx_sessions_expires ON sessions(datetime(expires_at));

EXPLAIN QUERY PLAN
SELECT id FROM sessions WHERE datetime(expires_at) < datetime('now');
-- SEARCH sessions USING INDEX idx_sessions_expires (<expr><?)

式索引が使われるのは、問い合わせの式が索引の式と同じ形のときだけです。書き方を変えたら、EXPLAIN QUERY PLAN で実行計画を確かめてください。

よくある質問

ISO 8601 の文字列どうしなら、そのまま比べてよい？

形式（区切り文字・小数秒の有無・時差の表記）が完全に揃っているなら、文字列の比較で正しい順になります。1つでも形の違う行が混ざる可能性があるなら、両辺を datetime() で包みます。

D1 でも同じことが起きる？

Cloudflare D1 は SQLite を土台にしているので、同じ関数と同じ比較の規則が使えます。Workers から toISOString() の値を書き、SQL の datetime('now') と比べる組み合わせは、特に混ざりやすい形です。

まとめ

  • SQLite に日時型は無い。TEXT で持つと、書く側によって T 区切りと空白区切りが混ざる
  • 混ざった列を文字列のまま比べると、同じ日付の行だけ、相手の形式しだいで「消しすぎ」も「残しすぎ」も起きる
  • 両辺を datetime() で揃えれば正しく比べられる。時差つきの値は UTC に直される
  • 読めない形式は NULL になり、比較から黙って外れる。先に数えておく
  • 秒より細かい順序が要るなら julianday() か unixepoch(値, 'subsec')
  • 並び替え（ORDER BY）も同じように間違う

参考

筆者（合同会社My Agent Works）は、宅建士試験の学習アプリ「宅建BOOST」（https://takken-boost.jp/）を開発しています。開発で得た知見を今後も投稿します。
X: https://x.com/takkenboost

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?