SQLite timestampとは？挿入・取得・比較方法を解説

SQLite timestampとは？挿入・取得・比較方法を徹底解説

はじめに：リレーショナルデータベースにおける日付/時刻データの重要性

データベースを扱う上で、日付や時刻の情報は非常に重要です。データの作成日時、更新日時、イベントの発生時刻、ユーザーのログイン履歴など、多くのアプリケーションで時間の要素は不可欠です。これらの情報を正確に記録し、効率的に操作できることは、システムの信頼性や機能性に直結します。

しかし、データベースシステムによっては、日付/時刻データの扱い方が異なります。特にSQLiteは、他の多くのリレーショナルデータベース（PostgreSQL, MySQL, SQL Serverなど）とは少し異なるアプローチをとっています。PostgreSQLのTIMESTAMP WITH TIME ZONE、MySQLのDATETIMEやTIMESTAMPといった専用のデータ型を持つのに対し、SQLiteには「TIMESTAMP」という名前の専用のデータ型は存在しません。

では、SQLiteで日付/時刻データを扱うにはどうすれば良いのでしょうか？そして、どのように挿入、取得、比較を行えば良いのでしょうか？この記事では、SQLiteにおける日付/時刻データの扱い方について、その推奨される方法、具体的な操作手順、そして強力な日付/時刻関数について、約5000語の詳細な解説を行います。SQLiteで時間に関わるデータを扱う際の疑問を解消し、効果的なデータベース設計と操作を行うための知識を深めていきましょう。

SQLiteにおける日付/時刻の扱い：専用型は存在しない？

前述の通り、SQLiteには標準的なSQLで定義されているような、厳密な意味での日付/時刻型（例: DATE, TIME, TIMESTAMP, DATETIME）は組み込み型として提供されていません。SQLiteは非常に柔軟な型アフィニティ（Type Affinity）システムを採用しており、カラムに指定された型名はあくまで「推奨」にすぎません。SQLiteは基本的に、格納される値の実際のデータ型（ストレージクラス）に基づいてデータを扱います。ストレージクラスには、NULL, INTEGER, REAL, TEXT, BLOBの5種類があります。

SQLiteのドキュメントでは、日付や時刻の値を格納するために、以下の3つのストレージクラスのいずれかを使用することを強く推奨しています。

1. TEXT: 日付や時刻をISO8601形式の文字列として格納します。例: "YYYY-MM-DD HH:MM:SS.SSS".
2. REAL: 日付をユリウス通日（Julian Day Number）として浮動小数点数で格納します。これは、グレゴリオ暦の紀元前4714年11月24日正午からの経過日数を表します。
3. INTEGER: 日付や時刻をUnixエポック秒（Unix Epoch time）として整数で格納します。これは、協定世界時（UTC）の1970年1月1日00:00:00からの経過秒数を表します。

これらの推奨される格納方法には、それぞれメリットとデメリットがあります。アプリケーションの要件や、日付/時刻データをどのように利用したいかに応じて、最適な方法を選択する必要があります。

各ストレージクラスのメリット・デメリット

ストレージクラス	格納形式	メリット	デメリット	主な用途
TEXT	ISO8601文字列 (例: “2023-10-27 10:30:00”)	人間が読みやすい、辞書順でソート・比較が可能	ストレージ効率がやや低い、数値計算が煩雑	可読性重視、表示が主体のデータ
REAL	ユリウス通日 (例: 2460245.9375)	日付間の計算が容易	人間が読みにくい	日付計算が頻繁な場合
INTEGER	Unixエポック秒 (例: 1698379800)	日付/時刻計算が最も容易（秒単位）、ストレージ効率が良い	人間が読みにくい、秒未満の精度は表現しにくい（ただしSQLite関数は小数部も扱える）、2038年問題の可能性（ただしSQLite関数内で回避される）	高速な日付/時刻計算、システムの内部処理用

SQLiteは、これらのストレージクラスで格納された日付/時刻データを操作するための強力な組み込み関数群（date, time, datetime, julianday, unixepoch, strftimeなど）を提供しています。これらの関数を使用することで、異なる形式間の変換や、日付/時刻に基づいた様々な計算、抽出、比較を行うことができます。

重要なのは、「TIMESTAMP」というカラム名を指定した場合でも、それは単なる型アフィニティのヒントに過ぎないという点です。SQLiteは、挿入される値の実際の型に基づいて、TEXT, REAL, または INTEGER のいずれかのストレージクラスに格納します。慣習的に、日付/時刻データを格納するカラムにはTIMESTAMP, DATETIME, DATEなどの型名を使用することが多いですが、これは他のデータベースシステムとの互換性や、開発者への意図伝達のためであり、SQLite内部での処理に必須ではありません。多くの場合、DATETIMEやTIMESTAMPといった型名は、SQLiteによってNUMERIC型アフィニティとして扱われ、挿入される値によって適切なストレージクラス（TEXT, REAL, INTEGER）が選択されます。しかし、意図的に特定の形式で格納したい場合は、挿入時に適切な関数や形式を使用することが重要です。

本記事では、SQLiteが推奨するこれらの3つの格納方法に焦点を当て、それぞれの挿入、取得、比較の方法を詳細に解説していきます。

TEXT型での日付/時刻データの扱い

TEXT型で日付/時刻データを格納する場合、一般的にはISO8601形式の文字列が推奨されます。ISO8601形式は、YYYY-MM-DD HH:MM:SS.SSS（年-月-日 時:分:秒.ミリ秒）のような形式で、人間にとって読みやすく、また辞書順でソートした場合に時系列順になるという大きな利点があります。

TEXT型での挿入方法

TEXT型カラムに日付/時刻データを挿入する最も簡単な方法は、ISO8601形式の文字列リテラルを直接指定することです。

“`sql
— テーブルの作成（例）
CREATE TABLE events (
event_id INTEGER PRIMARY KEY AUTOINCREMENT,
event_name TEXT,
event_time TEXT — TEXT型で日時を格納
);

— 文字列リテラルでの挿入
INSERT INTO events (event_name, event_time)
VALUES (‘Meeting Start’, ‘2023-10-27 14:00:00’);

INSERT INTO events (event_name, event_time)
VALUES (‘Lunch Break’, ‘2023-10-27 12:30:00.500’); — ミリ秒を含めることも可能

— タイムゾーン情報を含める場合（ZはUTCを表す）
INSERT INTO events (event_name, event_time)
VALUES (‘UTC Event’, ‘2023-10-27T15:00:00Z’); — ISO8601ではTで区切ることも多い
“`

また、SQLiteの組み込み関数を使用して、現在の日付/時刻を挿入することも頻繁に行われます。datetime('now')関数は、現在の協定世界時（UTC）をISO8601形式のTEXTとして返します。デフォルトでは秒単位ですが、精度を指定することも可能です。

“`sql
— 現在時刻（UTC）の挿入
INSERT INTO events (event_name, event_time)
VALUES (‘Record Creation’, datetime(‘now’));

— 現在時刻（ローカルタイム）の挿入
INSERT INTO events (event_name, event_time)
VALUES (‘Local Timestamp’, datetime(‘now’, ‘localtime’)); — ‘localtime’修飾子を使用
“`

strftime()関数は、指定した日付/時刻値を様々な形式の文字列に変換できます。これを利用して、特定の形式で日時を挿入することも可能です。

sql
-- strftimeを使って特定の形式で挿入
-- 例えば、秒以下の精度も保持したい場合
INSERT INTO events (event_name, event_time)
VALUES ('Precise Time', strftime('%Y-%m-%d %H:%M:%f', 'now')); -- %f は HH:MM:SS.SSS 形式

既存の日付/時刻データ（他の形式や他のカラムの値）をTEXT型に変換して挿入する場合も、strftime()関数が便利です。

sql
-- 例えば、REAL型（ユリウス通日）のカラムからTEXT型に変換して挿入
-- （実際のテーブル設計としては一般的ではないが、変換例として）
-- 仮に original_julian_day_col が REAL 型のカラムとする
-- INSERT INTO new_text_table (text_time_col)
-- SELECT strftime('%Y-%m-%d %H:%M:%S', original_julian_day_col) FROM original_table;

TEXT型で格納する際の注意点として、挿入する文字列が有効なISO8601形式でない場合、SQLiteの組み込み関数で正しく認識されない可能性があります。一貫した形式でデータを格納することが重要です。

TEXT型での取得方法

TEXT型で格納された日付/時刻データは、そのままSELECT文で取得すれば、格納時の文字列として取得できます。

sql
-- TEXT型のイベントタイムを取得
SELECT event_id, event_name, event_time FROM events;

取得した文字列は人間が読みやすいため、多くの場合はそのまま表示に利用できます。しかし、表示形式を変えたい場合や、日付の特定の部分（年、月、日など）だけを取り出したい場合は、再びstrftime()関数が役立ちます。

strftime()関数は、第一引数に目的のフォーマット文字列を、第二引数以降に変換したい日付/時刻値を指定します。

“`sql
— 年-月-日 の形式で取得
SELECT event_id, event_name, strftime(‘%Y-%m-%d’, event_time) AS event_date
FROM events;

— 時:分:秒 の形式で取得
SELECT event_id, event_name, strftime(‘%H:%M:%S’, event_time) AS event_hour_minute_second
FROM events;

— 曜日（例: Mon, Tue…）を取得
SELECT event_id, event_name, strftime(‘%w’, event_time) AS weekday_number, — 0=日曜日, 6=土曜日
strftime(‘%a’, event_time) AS weekday_abbr
FROM events;

— 月名（例: Jan, Feb…）を取得
SELECT event_id, event_name, strftime(‘%m’, event_time) AS month_number,
strftime(‘%b’, event_time) AS month_abbr
FROM events;

— 他の形式に変換して取得（REAL型やINTEGER型に変換）
— ユリウス通日として取得
SELECT event_id, event_name, julianday(event_time) AS julian_day_value
FROM events;

— Unixエポック秒として取得
SELECT event_id, event_name, unixepoch(event_time) AS unix_epoch_seconds
FROM events;
“`

このように、strftime()関数や他の日付/時刻関数を使用することで、TEXT型で格納された日付/時刻データを柔軟に取得し、様々な形式や他のデータ型に変換して利用できます。

TEXT型での比較方法

TEXT型で格納されたISO8601形式の日付/時刻文字列は、辞書順での比較が時系列順の比較と一致するため、文字列としてそのまま比較演算子（<, >, =, <=, >=）やBETWEEN句を使用して比較できます。

“`sql
— 特定の日付より後のイベントを取得
SELECT event_id, event_name, event_time
FROM events
WHERE event_time > ‘2023-10-27 13:00:00’;

— 特定の時間範囲内のイベントを取得
SELECT event_id, event_name, event_time
FROM events
WHERE event_time BETWEEN ‘2023-10-27 12:00:00’ AND ‘2023-10-27 15:00:00’;

— 特定の日付のイベントを取得（LIKE演算子を使用する場合、形式に注意）
SELECT event_id, event_name, event_time
FROM events
WHERE event_time LIKE ‘2023-10-27%’; — 日付部分が ‘2023-10-27’ で始まるものを取得
“`

LIKE演算子を使った日付の比較は便利ですが、常にISO8601形式で格納されていることを前提とします。また、時間部分を考慮しない場合は、より正確な比較のために日付関数を使用する方が安全です。

例えば、時間部分を無視して日付部分のみで比較したい場合は、date()関数やstrftime()関数を使用して比較用の値を生成します。

“`sql
— 日付部分のみで比較（date()関数を使用）
SELECT event_id, event_name, event_time
FROM events
WHERE date(event_time) = ‘2023-10-27’; — event_timeの日付部分が ‘2023-10-27’ と等しいか

— 日付部分のみで比較（strftime()関数を使用）
SELECT event_id, event_name, event_time
FROM events
WHERE strftime(‘%Y-%m-%d’, event_time) = ‘2023-10-27’;
“`

これらの関数を使うことで、時間情報を含むTEXT型の値から日付部分だけを取り出して比較できます。これは、特定の日の全てのイベントを取得したい場合などに非常に便利です。

さらに、日付/時刻関数を使って他の形式に変換してから比較することも可能です。例えば、ユリウス通日やUnixエポック秒に変換すれば、数値としての比較になります。これは、特に日付/時刻計算の結果と比較する場合などに有効です。

sql
-- Unixエポック秒に変換して比較
-- 例: 2023年10月27日 14:30:00 より後のイベントを取得
-- UNIXEPOCH('2023-10-27 14:30:00') は対応するUnixエポック秒を返す
SELECT event_id, event_name, event_time
FROM events
WHERE unixepoch(event_time) > unixepoch('2023-10-27 14:30:00');

TEXT型での比較は直感的で分かりやすい反面、格納形式の一貫性が非常に重要です。異なる形式の文字列が混在していると、期待通りの比較結果が得られない可能性があります。そのため、TEXT型で格納する場合は、常にISO8601形式を使用することを強く推奨します。

REAL型での日付/時刻データの扱い

REAL型で日付/時刻データを格納する場合、ユリウス通日（Julian Day Number）が使用されます。ユリウス通日とは、紀元前4714年11月24日正午からの経過日数を表す数値です。この形式の最大の利点は、日付や時刻の計算が数値演算として非常に簡単に行える点です。例えば、2つの日付間の日数を計算したり、特定の日数だけ進んだり戻ったりした日付を求めたりすることが容易になります。

REAL型での挿入方法

REAL型カラムに日付/時刻データを挿入する場合、主にjulianday()関数を使用して、他の形式（TEXTやnow）からユリウス通日を生成して挿入します。

“`sql
— テーブルの作成（例）
CREATE TABLE tasks (
task_id INTEGER PRIMARY KEY AUTOINCREMENT,
task_name TEXT,
due_date REAL — REAL型で期日を格納（ユリウス通日）
);

— 現在の日付をユリウス通日として挿入
INSERT INTO tasks (task_name, due_date)
VALUES (‘Finish Report’, julianday(‘now’));

— 特定の日付/時刻文字列をユリウス通日として挿入
INSERT INTO tasks (task_name, due_date)
VALUES (‘Submit Proposal’, julianday(‘2023-11-15 17:00:00’));

— 日付と時間部分を分けて指定することも可能
INSERT INTO tasks (task_name, due_date)
VALUES (‘Schedule Meeting’, julianday(‘2023-11-01′, ’10:00’));
“`

julianday()関数は、引数として日付/時刻文字列、now、または他の日付/時刻関数（date, time, datetime, unixepoch, 他のjuliandayの戻り値）を受け取ります。また、localtimeやutcといった修飾子を使用して、ローカルタイムやUTCでのユリウス通日を取得することも可能です。

sql
-- 現在のローカルタイムをユリウス通日として挿入
INSERT INTO tasks (task_name, due_date)
VALUES ('Local Deadline', julianday('now', 'localtime'));

リテラル数値（ユリウス通日）を直接挿入することも理論上は可能ですが、ユリウス通日は人間にとって直感的でないため、通常は関数を使って挿入値を生成します。

sql
-- ユリウス通日のリテラル値を挿入（非推奨、例としてのみ）
-- INSERT INTO tasks (task_name, due_date)
-- VALUES ('Arbitrary Date', 2460245.5); -- これは '2023-10-27 00:00:00 UTC' のユリウス通日

REAL型での取得方法

REAL型で格納されたユリウス通日は、そのままSELECT文で取得すると浮動小数点数として表示されます。

sql
-- REAL型の期日を取得（ユリウス通日として）
SELECT task_id, task_name, due_date FROM tasks;

取得したユリウス通日を人間が読める形式に変換するには、strftime(), date(), time(), datetime()といった関数を使用します。これらの関数は、ユリウス通日値を引数として受け取り、目的の形式の文字列や他の日付/時刻値を返します。

“`sql
— ユリウス通日をISO8601形式の文字列に変換して取得
SELECT task_id, task_name, datetime(due_date) AS due_datetime_text
FROM tasks;

— ユリウス通日を特定フォーマットの文字列に変換して取得
SELECT task_id, task_name, strftime(‘%Y-%m-%d %H:%M’, due_date) AS formatted_due_date
FROM tasks;

— ユリウス通日から日付部分のみを取得
SELECT task_id, task_name, date(due_date) AS due_date_only
FROM tasks;

— ユリウス通日から時間部分のみを取得
SELECT task_id, task_name, time(due_date) AS due_time_only
FROM tasks;

— ユリウス通日をUnixエポック秒に変換して取得
SELECT task_id, task_name, unixepoch(due_date) AS due_date_unix_epoch
FROM tasks;
“`

date(), time(), datetime()関数は、第一引数に変換したい日付/時刻値を、第二引数以降に修飾子を取ります。localtime修飾子を指定すると、取得したユリウス通日をローカルタイムに変換して表示します。

“`sql
— ユリウス通日をローカルタイムのISO8601文字列に変換して取得
SELECT task_id, task_name, datetime(due_date, ‘localtime’) AS due_datetime_local
FROM tasks;

— ユリウス通日からローカルタイムの日付部分を取得
SELECT task_id, task_name, date(due_date, ‘localtime’) AS due_date_only_local
FROM tasks;
“`

REAL型で格納されたユリウス通日は、そのままでは人間にとって意味を理解しにくいですが、SQLiteの強力な日付/時刻関数群を使うことで、柔軟に表示や計算に利用できます。

REAL型での比較方法

REAL型で格納されたユリウス通日は、単なる数値であるため、数値としての比較演算子（<, >, =, <=, >=）やBETWEEN句を使用して簡単に比較できます。ユリウス通日は時系列順に増加するため、数値の比較がそのまま日付/時刻の比較になります。

“`sql
— 特定の日付（ユリウス通日）より後の期日のタスクを取得
— julianday(‘2023-11-10’) は ‘2023-11-10 00:00:00 UTC’ のユリウス通日を返す
SELECT task_id, task_name, datetime(due_date) AS due_datetime
FROM tasks
WHERE due_date > julianday(‘2023-11-10’);

— 特定の期間（ユリウス通日）内の期日のタスクを取得
— BETWEEN句でユリウス通日の範囲を指定
SELECT task_id, task_name, datetime(due_date) AS due_datetime
FROM tasks
WHERE due_date BETWEEN julianday(‘2023-11-01’) AND julianday(‘2023-11-30 23:59:59.999’);
“`

比較の際に、文字列や他の形式の日付/時刻値を直接REAL型のカラムと比較する場合、SQLiteはその値を自動的にユリウス通日に変換しようと試みます。しかし、明示的にjulianday()関数を使用する方が、意図が明確になり、予期せぬ結果を防ぐためにより安全で推奨されます。

“`sql
— 非推奨（SQLiteが暗黙的に変換を試みるが、明示的でない）
— WHERE due_date > ‘2023-11-10’;

— 推奨（明示的に変換）
WHERE due_date > julianday(‘2023-11-10’);
“`

ユリウス通日での比較は、特に期間計算の結果など、他の数値との比較が必要な場合にその威力を発揮します。例えば、「期日が今日から7日以内」といった条件は、数値計算で簡単に表現できます。

sql
-- 期日が今日から7日以内のタスクを取得
-- julianday('now') は現在のユリウス通日
-- julianday('now', '+7 days') は現在から7日後のユリウス通日
SELECT task_id, task_name, datetime(due_date) AS due_datetime
FROM tasks
WHERE due_date BETWEEN julianday('now') AND julianday('now', '+7 days');

このように、REAL型（ユリウス通日）での日付/時刻データの比較は、数値演算の直感を活かせる場合に非常に効率的です。

INTEGER型での日付/時刻データの扱い

INTEGER型で日付/時刻データを格納する場合、Unixエポック秒（Unix Epoch time）が使用されるのが一般的です。Unixエポック秒とは、協定世界時（UTC）の1970年1月1日00:00:00からの経過秒数を表す整数値です。この形式は、特にシステム内部での日付/時刻計算に非常に適しており、ストレージ効率も高いという利点があります。

INTEGER型での挿入方法

INTEGER型カラムにUnixエポック秒を挿入する場合、主にunixepoch()関数を使用して、他の形式（TEXTやnow）からUnixエポック秒を生成して挿入します。

“`sql
— テーブルの作成（例）
CREATE TABLE logs (
log_id INTEGER PRIMARY KEY AUTOINCREMENT,
message TEXT,
log_timestamp INTEGER — INTEGER型でタイムスタンプを格納（Unixエポック秒）
);

— 現在時刻（UTC）をUnixエポック秒として挿入
INSERT INTO logs (message, log_timestamp)
VALUES (‘Application started’, unixepoch(‘now’));

— 特定の日付/時刻文字列をUnixエポック秒として挿入
INSERT INTO logs (message, log_timestamp)
VALUES (‘User logged in’, unixepoch(‘2023-10-27 14:45:30’));

— ローカルタイムをUnixエポック秒として挿入（’localtime’修飾子を使用）
INSERT INTO logs (message, log_timestamp)
VALUES (‘Background process started’, unixepoch(‘now’, ‘localtime’));
“`

unixepoch()関数は、引数として日付/時刻文字列、now、または他の日付/時刻関数（date, time, datetime, julianday, 他のunixepochの戻り値）を受け取ります。また、localtimeやutcといった修飾子を使用して、ローカルタイムやUTCでのUnixエポック秒を取得することも可能です。

リテラル数値（Unixエポック秒）を直接挿入することも、その値を知っていれば可能です。

sql
-- Unixエポック秒のリテラル値を挿入
-- 1698381930 は 2023-10-27 14:45:30 UTC を表す
INSERT INTO logs (message, log_timestamp)
VALUES ('Manual entry', 1698381930);

Unixエポック秒は整数なので、秒単位以下の精度を直接表現することは難しいですが、SQLiteのstrftime('%s', ...)やunixepoch()関数は、内部的には秒の小数点以下も扱うことが可能です。ただし、これをINTEGERカラムに格納すると小数点以下は切り捨てられます。より高い精度が必要な場合は、TEXT型（ISO8601 with .SSS）やREAL型（ユリウス通日）を検討するか、別途ミリ秒やマイクロ秒を格納するカラムを用意する必要があります。しかし、多くの用途では秒単位の精度で十分です。

INTEGER型で日付/時刻データを扱う際に考慮すべき点として、2038年問題があります。Unixエポック秒を32ビット符号付き整数で格納しているシステムでは、2038年1月19日03:14:07 UTC以降の日時を表現できなくなる問題です。しかし、SQLiteのunixepoch()関数や関連する日付/時刻関数は、内部的にはより大きな整数型や浮動小数点数を使用してこの問題を回避しており、広い範囲の日時（おおよそ紀元前4713年から西暦5000年まで）を扱うことが可能です。したがって、SQLite自体を使う上でこの問題を直接気にする必要は少ないですが、アプリケーション層でこのINTEGER値を扱う際に、使用しているプログラミング言語やライブラリによっては注意が必要な場合があります。SQLiteのINTEGER型自体は64ビット整数を格納できるため、カラムに格納する上での2038年問題は通常発生しません。

INTEGER型での取得方法

INTEGER型で格納されたUnixエポック秒は、そのままSELECT文で取得すると整数値として表示されます。

sql
-- INTEGER型のタイムスタンプを取得（Unixエポック秒として）
SELECT log_id, message, log_timestamp FROM logs;

取得したUnixエポック秒を人間が読める形式に変換するには、strftime(), date(), time(), datetime()といった関数を使用します。これらの関数は、Unixエポック秒値を引数として受け取り、目的の形式の文字列や他の日付/時刻値を返します。

“`sql
— Unixエポック秒をISO8601形式の文字列（UTC）に変換して取得
SELECT log_id, message, datetime(log_timestamp, ‘utc’) AS log_datetime_utc
FROM logs;

— Unixエポック秒をISO8601形式の文字列（ローカルタイム）に変換して取得
SELECT log_id, message, datetime(log_timestamp, ‘localtime’) AS log_datetime_local
FROM logs;

— Unixエポック秒を特定フォーマットの文字列に変換して取得
SELECT log_id, message, strftime(‘%Y/%m/%d %H:%M:%S’, log_timestamp, ‘localtime’) AS formatted_log_datetime
FROM logs;

— Unixエポック秒から日付部分のみを取得
SELECT log_id, message, date(log_timestamp, ‘localtime’) AS log_date_only
FROM logs;

— Unixエポック秒から時間部分のみを取得
SELECT log_id, message, time(log_timestamp, ‘localtime’) AS log_time_only
FROM logs;

— Unixエポック秒をユリウス通日に変換して取得
SELECT log_id, message, julianday(log_timestamp, ‘utc’) AS log_timestamp_julian_day
FROM logs;
“`

date(), time(), datetime(), julianday()関数は、引数としてUnixエポック秒値を受け取ります。デフォルトではUTCとして扱われますが、localtime修飾子を指定することで、取得したUnixエポック秒をローカルタイムとして解釈し、そのローカルタイムに対応する日付/時刻を返します。

strftime()関数を使用する場合、第二引数にUnixエポック秒値を指定し、必要に応じてlocaltimeまたはutc修飾子を指定します。 %sフォーマットコードを使うと、入力された日付/時刻値をUnixエポック秒として取得できますが、これは通常、他の形式からUnixエポック秒に変換する際に使用し、既にUnixエポック秒で格納されている値を再びUnixエポック秒として取得するのにはあまり意味がありません。むしろ、Unixエポック秒を他の形式に変換するためにstrftime()を使うのが一般的です。

INTEGER型での比較方法

INTEGER型で格納されたUnixエポック秒は、単なる整数であるため、数値としての比較演算子（<, >, =, <=, >=）やBETWEEN句を使用して簡単に比較できます。Unixエポック秒は時系列順に増加するため、数値の比較がそのまま日付/時刻の比較になります。

“`sql
— 特定の時刻（Unixエポック秒）より後のログを取得
— unixepoch(‘2023-10-27 14:00:00’) は ‘2023-10-27 14:00:00 UTC’ のUnixエポック秒を返す
SELECT log_id, message, datetime(log_timestamp, ‘utc’) AS log_datetime
FROM logs
WHERE log_timestamp > unixepoch(‘2023-10-27 14:00:00’);

— 特定の時間範囲（Unixエポック秒）内のログを取得
— BETWEEN句でUnixエポック秒の範囲を指定
SELECT log_id, message, datetime(log_timestamp, ‘utc’) AS log_datetime
FROM logs
WHERE log_timestamp BETWEEN unixepoch(‘2023-10-27 12:00:00’) AND unixepoch(‘2023-10-27 15:00:00’);
“`

比較の際に、文字列や他の形式の日付/時刻値を直接INTEGER型のカラムと比較する場合、SQLiteはその値を自動的にUnixエポック秒に変換しようと試みます。しかし、明示的にunixepoch()関数を使用する方が、意図が明確になり、予期せぬ結果を防ぐためにより安全で推奨されます。

“`sql
— 非推奨（SQLiteが暗黙的に変換を試みるが、明示的でない）
— WHERE log_timestamp > ‘2023-10-27 14:00:00’;

— 推奨（明示的に変換）
WHERE log_timestamp > unixepoch(‘2023-10-27 14:00:00’);
“`

Unixエポック秒での比較は、特に期間計算の結果など、他の数値との比較が必要な場合にその威力を発揮します。例えば、「過去1時間のログを取得」といった条件は、現在時刻のUnixエポック秒から3600を引いた値と比較することで簡単に実現できます。

sql
-- 過去1時間のログを取得
SELECT log_id, message, datetime(log_timestamp, 'localtime') AS log_datetime
FROM logs
WHERE log_timestamp >= unixepoch('now', '-1 hour'); -- 現在時刻（UTC）から1時間前のUnixエポック秒

このように、INTEGER型（Unixエポック秒）での日付/時刻データの比較は、数値演算が非常に効率的に行える場合に有効です。

SQLiteの日付/時刻関数（Date and Time Functions）の詳細

SQLiteが提供する日付/時刻関数は非常に強力で、上記の3つの格納形式の間で相互変換を行ったり、日付/時刻の計算を行ったり、特定の部分を抽出したりするのに不可欠です。ここでは、主要な日付/時刻関数と、その重要な機能である「修飾子（Modifiers）」について詳しく解説します。

SQLiteの日付/時刻関数は、第一引数に日時値を取ります。この日時値は、以下のいずれかの形式で指定できます。

• ISO8601形式の文字列 ('YYYY-MM-DD HH:MM:SS.SSS')
• YYYY-MM-DD形式の日付文字列 ('2023-10-27')
• HH:MM:SS.SSS形式の時間文字列 ('14:30:00')
• nowキーワード (現在のUTC日時を表す)
• ユリウス通日（REAL値）
• Unixエポック秒（INTEGER値）

第二引数以降には、1つ以上の修飾子を指定できます。修飾子は、日時値に様々な操作（時間の加算/減算、タイムゾーン変換など）を適用するために使用されます。

主要な日付/時刻関数

1. date(timestring, modifier, modifier, ...)

   • 指定された日時値から、YYYY-MM-DD形式の日付文字列を返します。
   • 時間部分は切り捨てられます。
   • 例: SELECT date('now'); -> 'YYYY-MM-DD' (現在のUTC日付)
   • 例: SELECT date('2023-10-27 14:30:00'); -> '2023-10-27'

2. time(timestring, modifier, modifier, ...)

   • 指定された日時値から、HH:MM:SS形式の時間文字列を返します（秒以下の精度は通常切り捨て）。
   • 例: SELECT time('now'); -> 'HH:MM:SS' (現在のUTC時間)
   • 例: SELECT time('2023-10-27 14:30:00.123'); -> '14:30:00'

3. datetime(timestring, modifier, modifier, ...)

   • 指定された日時値から、YYYY-MM-DD HH:MM:SS形式の日付と時間の文字列を返します（秒以下の精度は通常切り捨て）。
   • 例: SELECT datetime('now'); -> 'YYYY-MM-DD HH:MM:SS' (現在のUTC日時)
   • 例: SELECT datetime('2023-10-27'); -> '2023-10-27 00:00:00'

4. julianday(timestring, modifier, modifier, ...)

   • 指定された日時値から、ユリウス通日（REAL値）を返します。
   • 最も高い精度（ミリ秒単位以上）を内部的に保持できる形式の一つです。
   • 例: SELECT julianday('now'); -> 現在のUTCユリウス通日 (例: 2460245.9375)
   • 例: SELECT julianday('2023-10-27 00:00:00'); -> 2460245.5

5. unixepoch(timestring, modifier, modifier, ...)

   • 指定された日時値から、Unixエポック秒（INTEGER値）を返します。
   • 引数に秒以下の精度が含まれる場合、小数点以下は切り捨てられます（ただし、SQLiteの内部処理は秒以下の精度も考慮可能）。
   • 例: SELECT unixepoch('now'); -> 現在のUTC Unixエポック秒 (例: 1698379800)
   • 例: SELECT unixepoch('2023-10-27 14:30:00'); -> 1698383400
   • Unixエポック秒はUTC基準です。ローカルタイムをUnixエポック秒に変換したい場合は、unixepoch('now', 'localtime')のように修飾子を使います。逆に、Unixエポック秒をローカルタイムの表示にしたい場合は、datetime(unixepoch_value, 'localtime')のように修飾子を使います。

6. strftime(format, timestring, modifier, modifier, ...)

   • 指定された日時値を、指定されたformat文字列に従って整形した文字列として返します。
   • 最も柔軟な関数であり、様々な表示形式に対応できます。
   • format文字列には、以下の様な変換指定子を使用できます。
     • %Y: 年 (YYYY)
     • %m: 月 (MM)
     • %d: 日 (DD)
     • %H: 時 (00-23)
     • %M: 分 (00-59)
     • %S: 秒 (00-59)
     • %f: 秒とミリ秒 (SS.SSS)
     • %w: 曜日（0=日曜日, 6=土曜日）
     • %j: 元旦からの日数 (001-366)
     • %W: 年間の週番号（月曜日を週の始まりとする）
     • %U: 年間の週番号（日曜日を週の始まりとする）
     • %H%M%S: 時分秒 (HHMMSS)
     • %Y%m%d: 年月日 (YYYYMMDD)
     • %%: リテラルの %
     • その他多数（標準Cライブラリのstrftimeに準拠）
   • 例: SELECT strftime('%Y-%m-%d %H:%M:%S', 'now'); -> 'YYYY-MM-DD HH:MM:SS' (現在のUTC日時)
   • 例: SELECT strftime('%H:%M', '2023-10-27 14:30:00'); -> '14:30'
   • 例: SELECT strftime('%Y/%m/%d', julianday('now')); -> 現在のUTC日付を YYYY/MM/DD 形式で

修飾子（Modifiers）の詳細

修飾子は、日時値に対して様々な調整を加えるために使用されます。複数の修飾子を指定することも可能です。修飾子は指定された順に適用されます。

主な修飾子:

• NNN days, NNN hours, NNN minutes, NNN seconds, NNN months, NNN years

  • 指定された日数、時間、分、秒、月数、年数を日時値に加算または減算します。NNNは整数または浮動小数点数です（浮動小数点数は日数にのみ意味がある）。減算は負の数で行います（例: -7 days）。
  • 例: SELECT datetime('now', '+7 days'); -> 現在から7日後のUTC日時
  • 例: SELECT date('2023-10-27', '-1 month'); -> 2023-09-27
  • 例: SELECT datetime('2023-10-27 14:30:00', '+2 hours', '-30 minutes'); -> 2023-10-27 16:00:00

• start of day

  • 日時値の時間をその日の開始（00:00:00）に丸めます。
  • 例: SELECT datetime('2023-10-27 14:30:00', 'start of day'); -> '2023-10-27 00:00:00'

• start of month

  • 日時値をその月の1日の開始（YYYY-MM-01 00:00:00）に丸めます。
  • 例: SELECT date('2023-10-27', 'start of month'); -> '2023-10-01'

• start of year

  • 日時値をその年の1月1日の開始（YYYY-01-01 00:00:00）に丸めます。
  • 例: SELECT date('2023-10-27', 'start of year'); -> '2023-01-01'

• start of quarter (SQLite 3.38.0以降)

  • 日時値をその四半期の開始日（00:00:00）に丸めます。
  • 例: SELECT date('2023-10-27', 'start of quarter'); -> '2023-10-01'

• weekday N (Nは0から6, 0が日曜日)

  • 日時値を、指定された曜日（N）の最も近い過去または未来の日付に調整します。デフォルトでは未来ですが、過去を探す場合は-7 daysなどのオフセットと組み合わせます。
  • 例: SELECT date('2023-10-27', 'weekday 0'); -> ‘2023-10-29’ (2023-10-27は金曜日、最も近い未来の日曜日)
  • 例: SELECT date('2023-10-27', '-7 days', 'weekday 0'); -> ‘2023-10-22’ (2023-10-27の7日前(10/20)から、最も近い過去の日曜日)

• unixepoch

  • 入力の日時値をUnixエポック秒として解釈します（TEXTやREALの入力に対して使用）。これにより、Unixエポック秒値に適用される修飾子（例: +NNN seconds）を適用できます。
  • 例: SELECT datetime(1698383400, 'unixepoch'); -> '2023-10-27 14:30:00' (1698383400をUnix秒として解釈)

• julianday

  • 入力の日時値をユリウス通日として解釈します（TEXTやINTEGERの入力に対して使用）。
  • 例: SELECT datetime(2460245.9375, 'julianday'); -> '2023-10-27 10:30:00' (2460245.9375をユリウス通日として解釈)

• localtime

  • UTCの日時値をローカルタイムに変換します。
  • 例: SELECT datetime('now', 'localtime'); -> 現在のローカル日時

• utc

  • ローカルタイムの日時値をUTCに変換します。デフォルトの挙動なので、通常明示的に指定する必要はありません。

修飾子の組み合わせ例:

“`sql
— 現在時刻（UTC）から3日後の午前9時（ローカルタイム）
SELECT datetime(‘now’, ‘localtime’, ‘+3 days’, ‘9:00’);

— 特定のUnixエポック秒値をローカルタイムとして解釈し、日付部分のみを取得
SELECT date(log_timestamp, ‘localtime’);

— 期日（ユリウス通日）から5日前の日付を計算
SELECT date(due_date, ‘-5 days’);

— ログタイムスタンプ（Unixエポック秒）をローカルタイムに変換し、秒以下の精度を含む文字列として取得
SELECT strftime(‘%Y-%m-%d %H:%M:%f’, log_timestamp, ‘unixepoch’, ‘localtime’);
— または unixepoch_valueをそのままstrftimeに渡し、localtime修飾子を使う
SELECT strftime(‘%Y-%m-%d %H:%M:%f’, log_timestamp, ‘localtime’); — これがより一般的
“`

これらの関数と修飾子を組み合わせることで、SQLiteは日付/時刻データの挿入、取得、そして最も重要な「計算」と「変換」において非常に強力な機能を提供します。格納形式がTEXT, REAL, INTEGERのいずれであっても、これらの関数を使って自由に相互変換や操作が可能です。

どのデータ型を選ぶべきか？

SQLiteで日付/時刻データを格納する際に、TEXT, REAL, INTEGERのどの型を選ぶかは、アプリケーションの要件に依存します。以下に、選択の指針を示します。

• TEXT型（ISO8601文字列）が適している場合:

  • データベースの内容を人間が頻繁に直接参照する必要がある（可読性最優先）。
  • 日付/時刻データの主な用途が表示であり、複雑な計算はアプリケーション側で行うことが多い。
  • 秒以下の精度（ミリ秒など）を正確に格納・保持したい。ISO8601形式は小数点以下を含むことができるため適しています。
  • 既存システムとの互換性で、特定の文字列形式に合わせる必要がある。
  • 比較は文字列としての辞書順比較で十分な場合。

• REAL型（ユリウス通日）が適している場合:

  • 日付間の「期間」や「差」を計算することが多い。ユリウス通日は日数を表すため、引き算で簡単に期間が計算できます。
  • 特定の日数だけ進んだり戻ったりした日付を頻繁に計算する必要がある。
  • 秒以下の精度（ミリ秒など）を高い精度で保持したい。ユリウス通日は浮動小数点数で秒以下の精度も表現できます。
  • 可読性は重視しない（取得時に変換すれば良いため）。

• INTEGER型（Unixエポック秒）が適している場合:

  • 日付/時刻間の「秒」単位の差や計算を頻繁に行う。Unixエポック秒は秒数を表すため、計算が非常に高速でシンプルです。
  • システムの内部処理やログ記録など、計算効率やストレージ効率が求められる場合。
  • Unixシステムや他のシステムとUnixエポック秒形式でデータをやり取りする必要がある。
  • 秒単位の精度で十分な場合。
  • 可読性は重視しない。

多くの一般的なWebアプリケーションやシンプルなデータ記録においては、TEXT型が最も直感的で扱いやすいかもしれません。人間が直接SQLでデータを参照する際に分かりやすいというメリットは大きいです。

一方、時系列データの分析や、イベント間の正確な経過時間を計算する必要があるシステムでは、INTEGER型（Unixエポック秒）が最も効率的です。

REAL型（ユリウス通日）は、日数単位での計算が必要な特定のニッチな用途で非常に役立ちますが、Unixエポック秒ほど広く使われている形式ではないため、INTEGER型の方が一般的かもしれません。

ベストプラクティス:

• 一つのテーブル内でデータ型を統一する: 同じテーブル内の日付/時刻カラムは、同じストレージクラスで格納するのが望ましいです。これにより、データの管理やクエリが単純化されます。異なる形式が混在すると、比較や計算が複雑になります。
• カラム名で意図を示す: 例えば、created_at TEXT, due_date_jd REAL, event_time_unix INTEGERのように、カラム名に格納形式を示すサフィックスを付けることで、開発者がそのカラムのデータの性質を理解しやすくなります。
• 格納形式をドキュメント化する: データベーススキーマの設計において、各日付/時刻カラムがどのストレージクラスで、どの形式（TEXTならISO8601など）で格納されるかを明確にドキュメント化しておくべきです。
• UTCを基準にする: 可能であれば、データベースにはUTCで日時を格納することを強く推奨します。タイムゾーンの違いによる問題を回避しやすくなります。表示時には必要に応じてローカルタイムに変換します。SQLiteの関数はデフォルトでUTCを扱い、localtime修飾子でローカルタイムとの変換が可能です。

日付/時刻データの操作例（応用編）

これまでに解説した挿入、取得、比較、そして日付/時刻関数を活用して、具体的な操作例を見ていきましょう。

1. 特定の日付範囲のデータ取得

TEXT型で格納されている event_time カラムから、特定の日付（例: 2023年10月27日）の全てのイベントを取得します。

sql
SELECT event_id, event_name, event_time
FROM events
WHERE date(event_time) = '2023-10-27';
-- または
WHERE strftime('%Y-%m-%d', event_time) = '2023-10-27';
-- または
WHERE event_time BETWEEN '2023-10-27 00:00:00' AND '2023-10-27 23:59:59.999';

REAL型で格納されている due_date カラムから、特定の日付範囲（例: 2023年11月1日から2023年11月15日）のタスクを取得します。

sql
SELECT task_id, task_name, datetime(due_date, 'localtime') AS due_datetime_local
FROM tasks
WHERE due_date BETWEEN julianday('2023-11-01') AND julianday('2023-11-15 23:59:59.999');

INTEGER型で格納されている log_timestamp カラムから、特定の期間（例: 過去24時間）のログを取得します。

sql
SELECT log_id, message, datetime(log_timestamp, 'localtime') AS log_datetime_local
FROM logs
WHERE log_timestamp >= unixepoch('now', '-24 hours');

2. 特定の時間帯のデータ取得

日付は問わず、毎日午後2時から午後3時の間に発生したイベントを取得します（TEXT型 event_time の場合）。

sql
SELECT event_id, event_name, event_time
FROM events
WHERE time(event_time) BETWEEN '14:00:00' AND '15:00:00';

3. 曜日や月の抽出と集計

TEXT型 event_time カラムを使って、曜日ごとのイベント数を集計します。

“`sql
SELECT strftime(‘%w’, event_time) AS weekday_number, — 0=日曜日, 6=土曜日
COUNT(*)
FROM events
GROUP BY weekday_number
ORDER BY weekday_number;

— 曜日名を直接表示したい場合（少し複雑になります）
SELECT CASE strftime(‘%w’, event_time)
WHEN ‘0’ THEN ‘Sunday’
WHEN ‘1’ THEN ‘Monday’
WHEN ‘2’ THEN ‘Tuesday’
WHEN ‘3’ THEN ‘Wednesday’
WHEN ‘4’ THEN ‘Thursday’
WHEN ‘5’ THEN ‘Friday’
WHEN ‘6’ THEN ‘Saturday’
END AS weekday_name,
COUNT(*)
FROM events
GROUP BY weekday_name
ORDER BY MIN(strftime(‘%w’, event_time)); — 集計結果の順番を曜日順に保つため
“`

月ごとのイベント数を集計します。

sql
SELECT strftime('%Y-%m', event_time) AS year_month,
COUNT(*)
FROM events
GROUP BY year_month
ORDER BY year_month;

4. 期間の計算（差の計算）

2つの日付/時刻値の間の日数を計算します。ユリウス通日（REAL型）が最も簡単です。

“`sql
— ‘2023-11-15’ と ‘2023-10-27’ の間の日数を計算
SELECT julianday(‘2023-11-15’) – julianday(‘2023-10-27’) AS days_difference; — 結果: 19.0

— 2つのタイムスタンプカラム（INTEGER型）の間の秒数を計算
— 仮に log_timestamp1 と log_timestamp2 がINTEGERカラムとする
— SELECT log_timestamp2 – log_timestamp1 AS seconds_difference FROM some_table;
“`

TEXT型やINTEGER型で格納されている場合でも、計算のために一時的にユリウス通日に変換して差を求めることができます。

sql
-- event_time1 と event_time2 がTEXT型カラムの場合の日数差
-- SELECT julianday(event_time2) - julianday(event_time1) AS days_difference FROM events_with_duration;

期間計算は、REAL型やINTEGER型で格納されている場合に最も効率的です。特に秒単位の計算はINTEGER型（Unixエポック秒）が最適です。

5. 日付/時刻の加算と減算

現在の日付から1ヶ月後の日付を計算します。

sql
SELECT date('now', '+1 month');

特定の期日（due_date REAL型）から5日前の日時を計算します。

sql
SELECT datetime(due_date, '-5 days', 'localtime') AS five_days_before_due;

ログタイムスタンプ（log_timestamp INTEGER型）から1時間後の日時を計算します。

sql
SELECT datetime(log_timestamp, '+1 hour', 'localtime') AS one_hour_after_log;

これらの応用例は、SQLiteの日付/時刻関数が提供する柔軟性とパワーを示しています。格納形式に関わらず、適切な関数と修飾子を使うことで、様々な日付/時刻関連の処理を実現できます。

注意点とベストプラクティス

SQLiteで日付/時刻データを効果的に扱うための追加の注意点とベストプラクティスをまとめます。

• タイムゾーンの扱い: SQLiteの日付/時刻関数は、デフォルトでUTCを基準に動作します。nowはUTC時刻を返します。ローカルタイムで扱いたい場合は、必ず'localtime'修飾子を使用してください。データベースに格納する際は、UTCで格納し、取得して表示する際にローカルタイムに変換するという方法が、タイムゾーン関連の問題を最小限に抑えるための一般的なベストプラクティスです。
• データ型の一貫性: 前述の通り、一つのテーブル内では同じ格納形式を使用することを強く推奨します。これにより、クエリの記述が容易になり、エラーの可能性が減ります。
• インデックスの利用: 日付/時刻カラムをWHERE句やORDER BY句で頻繁に参照する場合、そのカラムにインデックスを作成することを検討してください。これにより、大量のデータに対するクエリパフォーマンスが大幅に向上する可能性があります。TEXT型の場合は文字列インデックス、REAL/INTEGER型の場合は数値インデックスが作成されます。日付/時刻関数をWHERE句で使用する場合（例: WHERE date(event_time) = '...'）、通常そのカラムのインデックスは利用されません。関数を使った条件でインデックスを効かせたい場合は、計算された値（例: date(event_time)の結果）を別途カラムに格納し、そこにインデックスを張るか、関数を使わない比較条件（例: WHERE event_time BETWEEN 'YYYY-MM-DD 00:00:00' AND 'YYYY-MM-DD 23:59:59'）を使用する必要があります。
• SQLiteのバージョン: SQLiteの日付/時刻関数は、古いバージョンでも基本的なものは利用可能ですが、新しい修飾子（例: start of quarter）や機能は新しいバージョンで追加されています。使用しているSQLiteのバージョンを確認し、利用可能な関数と修飾子を把握しておくと良いでしょう。
• 閏秒や閏年: SQLiteの日付/時刻関数は、標準的なグレゴリオ暦に基づいており、ほとんどの一般的な用途で正確な日付/時刻計算を行います。ただし、閏秒のような特殊なケースは標準的には考慮されません。ほとんどのアプリケーションでは問題になりませんが、天文学的な精度が求められる場合は注意が必要です。閏年は正しく扱われます。
• NULL値: 日付/時刻カラムにNULL値が挿入された場合、日付/時刻関数にNULLを渡すと通常NULLが返されます。クエリでNULL値を扱う際は、IS NULLやIS NOT NULLを使用してください。
• 文字列形式の厳密性 (TEXT型): TEXT型で格納する場合、ISO8601形式以外の文字列を格納すると、日付/時刻関数が正しく解釈できない可能性があります。常に一貫した形式で格納することを徹底してください。SQLiteの日付/時刻関数は、様々な文字列形式をある程度パースできますが、ISO8601が最も信頼できます。

まとめ

SQLiteには、PostgreSQLやMySQLのような専用のTIMESTAMPデータ型は存在しませんが、その柔軟な型アフィニティシステムと強力な組み込み関数群により、日付/時刻データを非常に効果的に扱うことができます。推奨される格納方法は以下の3つです。

1. TEXT: ISO8601形式の文字列 ('YYYY-MM-DD HH:MM:SS.SSS')
2. REAL: ユリウス通日（Julian Day Number）
3. INTEGER: Unixエポック秒（Unix Epoch time）

それぞれの形式には可読性、計算効率、ストレージ効率などのメリット・デメリットがあり、アプリケーションの要件に応じて最適なものを選択することが重要です。

• 挿入: datetime('now'), julianday('now'), unixepoch('now') といった関数や、対応する形式の文字列/数値リテラルを使用して行います。
• 取得: 格納した形式でそのまま取得できますが、strftime(), date(), time(), datetime(), julianday(), unixepoch()といった関数を使用して、他の形式への変換や特定の情報の抽出を行うことが一般的です。
• 比較: TEXT型の場合は文字列比較、REAL/INTEGER型の場合は数値比較が基本ですが、日付/時刻関数を使って比較用の値を生成したり、異なる形式に変換してから比較したりすることも頻繁に行われます。特にjulianday()やunixepoch()に変換しての数値比較は、期間計算などに非常に有効です。

SQLiteの日付/時刻関数とその修飾子は、これらの格納形式間で自由に相互変換を行ったり、日付/時刻の加算・減算といった計算を行ったりするための鍵となります。'localtime'や'utc'といった修飾子は、タイムゾーンの扱いにおいて重要な役割を果たします。

SQLiteで日付/時刻を扱う際は、単に「TIMESTAMP」という型名をカラムに指定するだけでなく、実際にどのストレージクラス（TEXT, REAL, INTEGER）で、どのような形式でデータを格納し、どのように取得・利用するかを明確に設計することが成功の鍵となります。適切な格納形式の選択、一貫性のあるデータ挿入、そして強力な日付/時刻関数の活用により、SQLiteでも多様で複雑な時間に関わる要件に対応することが可能です。

この記事が、SQLiteにおける日付/時刻データの理解を深め、実際の開発に役立つ一助となれば幸いです。