SQLite の自動タイムスタンプ

INSERT時に列を省略すると、DEFAULT CURRENT_TIMESTAMP を宣言した列にSQLiteが作成時刻を自動設定します。列に明示的に NULL を指定した場合はデフォルト値が適用されず、NOT NULL 制約があればINSERTは失敗します。SQLiteには専用の日付・時刻ストレージクラスがないため、TEXT、INTEGER、REALのいずれかを選び、一貫して使います。Pythonの sqlite3 が datetime オブジェクトを返すのは、変換を設定した場合だけです。

SQLiteのテーブルにタイムスタンプを自動で追加する

たとえば、タイムスタンプ列を DEFAULT CURRENT_TIMESTAMP と宣言します。例: CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);。INSERT INTO events (name) VALUES ('launch'); のように created_at を省略すると、SQLiteはデフォルト値を使います。INSERTで created_at を明示して NULL を指定した場合、デフォルト値は適用されず、NOT NULL 制約によってINSERTは拒否されます。後続のクエリでも同じテーブル名と列名を使います。例: SELECT id, name, created_at FROM events;。

CURRENT_TIMESTAMP は、現在のUTC日時を YYYY-MM-DD HH:MM:SS 形式のテキストで返すSQLiteのキーワードです。デフォルト値が適用されるのは行の挿入時であり、別の列を更新しても自動では変わりません。更新時刻が必要な場合は、UPDATE文で設定するか、その動作を行うトリガーを使います。

SQLiteで日時の表現形式を選ぶ

SQLiteの値は、TEXT、INTEGER、REAL のストレージクラスを使います（ほかに NULL と BLOB があります）。DATETIME や TIMESTAMP のような型名を宣言しても、ネイティブの日付・時刻ストレージ型が作られるわけではありません。値の保存方法にはSQLiteの型アフィニティ規則が適用されるため、表現形式を選んで一貫して使いましょう。

テキストは読みやすく、たとえば YYYY-MM-DD HH:MM:SS.SSS のように、大きな日付の単位から小さな単位へ並べた形式に適しています。整数にはUnix時刻を秒単位で保存でき、実数にはユリウス日を保存できます。SQLiteの日付・時刻関数は、これらの表現を解釈できます。DATE_ADD と UNIX_TIMESTAMP はSQLiteの関数ではありません。SQLiteで利用できる変換には、date()、time()、datetime() を使います。

SQLiteのタイムスタンプをPythonのdatetimeオブジェクトとして取得する

sqlite3 モジュールは、通常、SQLiteの値をストレージクラスに応じたPythonの値として返します。タイムスタンプのように見える文字列も、通常は str として返されます。Pythonの datetime として取得するには、コンバーターを登録し、宣言型の解析を有効にします。この単独で動作する例では、インメモリデータベースを作成し、固定のタイムスタンプを挿入して、変換後の値と型を表示します。

次の自己完結型の例では変換を設定し、クエリでタイムスタンプがPythonの datetime オブジェクトとして返されることを示します。

import datetime
import sqlite3


def convert_timestamp(raw_value):
    return datetime.datetime.fromisoformat(raw_value.decode("utf-8"))


sqlite3.register_converter("timestamp", convert_timestamp)
connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
connection.execute("CREATE TABLE events (created_at TIMESTAMP NOT NULL)")
connection.execute("INSERT INTO events VALUES (?)", ("2024-02-03 04:05:06",))
created_at = connection.execute("SELECT created_at FROM events").fetchone()[0]
print(created_at.isoformat(sep=" "))
print(type(created_at).__name__)
connection.close()

2024-02-03 04:05:06
datetime

変換は接続に対して設定するPython側の動作です。SQLite内では、値は引き続きSQLiteの保存規則に従って保存されます。コンバーターを登録していない場合や解析を有効にしていない場合は、値をテキストとして取得し、必要に応じてアプリケーションコードで解析してください。

SQLiteでUnixタイムスタンプを日付と時刻に変換する

整数のUnixタイムスタンプを、unixepoch 修飾子とともにSQLiteの日付・時刻関数に渡します。固定値の場合、datetime(1793956207, 'unixepoch') は日時を、date(1793956207, 'unixepoch') は日付を、time(1793956207, 'unixepoch') は時刻を返します。いずれも同じ時点を表し、返す要素だけが異なります。現在時刻を使う式は評価するタイミングによって変わるため、再現可能な例にするには固定値を使います。

日付と時刻をテキストとして保存する

2019-02-02 11:21:08.321 のようなテキスト表現は、YYYY-MM-DD HH:MM:SS.SSS の形式に従います。これはSQLiteの TEXT ストレージクラスに保存する書式上の慣例であり、特別な日付・時刻型ではありません。行ごとに形式、精度、タイムゾーンの扱いを統一してください。日付の大きな単位から順に並ぶ形式を一貫して使えば、テキストを辞書順に比較して時系列順を得られます。形式やタイムゾーンの扱いが混在すると、テキスト比較で誤解を招くことがあります。