SQLiteの数値日時は修飾子なしとunixepochでどう違う？

数値を渡しただけではUnix時刻にならない

SQLiteで日時を扱うときは、「保存した数値の意味」と「日時関数が数値をどう読むか」を分けて考える必要がある。整数で保存した値を日時関数に渡しても、その整数がUnixタイムスタンプとして解釈されるとは限らない。

公式ドキュメントでは、日時を表す形式として、ISO-8601の文字列、ユリウス日、Unixタイムスタンプが挙げられている。ユリウス日は日数、Unixタイムスタンプは秒数で日時を表す。同じ数値形式でも、単位と基準になる日時が異なる。

日時関数はISO-8601文字列とユリウス日を受け取る。修飾子に対応する関数では、'auto'または'unixepoch'を追加することでUnixタイムスタンプも受け取れる。数値を渡すSQLでは、関数名とともに、入力の解釈を指定する修飾子を確認したい。SQLite公式「Date And Time Functions」

公式が定める保存形式と入力の解釈

SQLiteには日時専用のストレージクラスがない。データ型の公式ドキュメントでは、日時の保存形式として次の対応が示されている。

ストレージクラス 日時の表現 内容
TEXT ISO8601文字列 YYYY-MM-DD HH:MM:SS.SSS形式など
REAL ユリウス日 先発グレゴリオ暦で紀元前4714年11月24日のグリニッジ正午からの日数
INTEGER Unix時刻 1970-01-01 00:00:00 UTCからの秒数

アプリケーションはこれらの形式を選んで日時を保存でき、組み込みの日時関数で形式を変換できる。SQLite公式「Datatypes In SQLite」

一方、日時関数のドキュメントでは、関数が受け取る日時の値をtime-valueと呼んでいる。ISO-8601文字列、ユリウス日、Unixタイムスタンプがこの呼び名にまとめられている。

公式に示されているtime-valueの例は、次のとおりだ。

形式 公式の例
ISO-8601文字列 '2025-05-29 14:16:00'
ユリウス日 2460825.09444444
Unixタイムスタンプ 1748528160

日時関数の説明では、ユリウス日には小数の日数、Unixタイムスタンプには小数の秒数も含められる。データ型の説明にある「INTEGERでUnix時刻を保存する」という対応と、日時関数が扱えるtime-valueの範囲は、分けて読む必要がある。SQLite公式「Date And Time Functions」

修飾子なしとunixepoch付きの比較

日時関数に数値形式のtime-valueを渡す場合、修飾子なしと'unixepoch'付きの違いは次のように整理できる。

入力の指定 数値の解釈 数値が表す単位・基準
修飾子なし ユリウス日 紀元前4714年11月24日のグリニッジ正午からの日数
数値の直後に'unixepoch' Unixタイムスタンプ 1970-01-01 00:00:00 UTCからの秒数

公式の入力形式一覧では、数値形式は整数または浮動小数点数で表すユリウス日とされている。その数値の直後に'auto'または'unixepoch'がある場合には、Unixタイムスタンプとしても解釈される。

ここで使う'unixepoch'は、入力された数値の意味を指定する修飾子である。整数という保存上の型だけでは、その数値が日数なのか秒数なのかは決まらない。日時関数に渡すときにも、保存した値の形式に対応する指定が必要になる。

たとえば、公式にあるUnixタイムスタンプの例をdatetime()へ渡すなら、次のように数値の直後へ修飾子を書く。

SELECT datetime(1748528160, 'unixepoch');

timediff()には、この指定方法を使えない。timediff()はtime-valueを二つだけ受け取り、修飾子を受け取らないためだ。利用できる入力はISO-8601文字列とユリウス日である。SQLite公式「Date And Time Functions」

入力の意味と欲しい戻り値から選ぶ

入力形式を確認したら、次に戻り値の形式を確認する。日時関数には文字列を返すものと数値を返すものがある。

関数 戻り値
date() YYYY-MM-DD形式の文字列
time() 通常はHH:MM:SS形式の文字列
datetime() 通常はYYYY-MM-DD HH:MM:SS形式の文字列
julianday() 小数部分を含むユリウス日
unixepoch() 通常はUnixタイムスタンプを表す整数の秒数

time()とdatetime()は、subsec修飾子を使うと、それぞれHH:MM:SS.SSS、YYYY-MM-DD HH:MM:SS.SSS形式になる。unixepoch()も、subsecを使うと小数秒を含む浮動小数点数を返す。

関数名のunixepoch()と、修飾子の'unixepoch'は役割が異なる。関数はUnixタイムスタンプを返すために使い、修飾子は入力の数値をUnixタイムスタンプとして受け取るために使う。

次の二つは、この役割の違いを確認するための例だ。

SELECT datetime(1748528160, 'unixepoch');
SELECT unixepoch('2025-05-29 14:16:00');

最初の式はUnixタイムスタンプを入力し、日時の文字列を返す関数を使っている。次の式は日時文字列を入力し、Unixタイムスタンプを返す関数を使っている。入力の指定と出力の形式を別々に読むことで、それぞれの役割を確認できる。SQLite公式「Date And Time Functions」

strftimeの書式と変換式を確認する

strftime()は、第1引数に指定した書式に従って日時を整形する関数である。公式の書式指定一覧では、%sは1970-01-01からの秒数、%Jは小数部分を含むユリウス日、%FはYYYY-MM-DD形式の日付、%TはHH:MM:SS形式の時刻と説明されている。

公式には、ほかの日時関数をstrftime()で表す対応表も掲載されている。

関数 公式に示されている対応する式
date(...) strftime('%F', ...)
time(...) strftime('%T', ...)
datetime(...) strftime('%F %T', ...)
julianday(...) CAST(strftime('%J', ...) AS REAL)
unixepoch(...) CAST(strftime('%s', ...) AS INT)

この表では、julianday()に対応する式にはAS REAL、unixepoch()に対応する式にはAS INTという明示的な変換が含まれている。

書式指定にはバージョンの注意点もある。素材に掲載された一覧はSQLite 3.46.0時点のもので、以前のバージョンではすべての指定に対応しているとは限らない。未定義または未対応の書式指定が含まれると、結果はNULLになる。SQLite公式「Date And Time Functions」

DATEやDATETIMEという型名と保存される値

SQLiteでは、値の型は値自体に関連付けられている。列の宣言型だけで値の型を決める仕組みとは異なり、列には型アフィニティという、保存するデータについての推奨される型がある。

STRICTではないテーブルでは、宣言型に含まれる文字列を順番に調べてアフィニティを決める。たとえば、INTを含む宣言型はINTEGER、CHAR、CLOB、TEXTを含む宣言型はTEXTとなる。

公式の対応表では、DATE、DATETIME、BOOLEANはいずれもNUMERICアフィニティに分類されている。これらの名前が、専用の日時や真偽値のストレージクラスを作るわけではない。

NUMERICアフィニティの列では、適切な整数または実数リテラルとして解釈できる文字列が、INTEGERやREALへ変換されることがある。公式の例では、文字列'3.0e+5'は整数で表現できるため、整数300000として保存される。整数または実数リテラルとして解釈できない文字列はTEXTのまま保存される。

したがって、DATEやDATETIMEという宣言とともに、実際に保存する値の形式も確認したい。日時文字列、ユリウス日、Unixタイムスタンプのどれを使っているかが、日時関数へ渡す際の入力指定に関わる。

なお、SQLiteには独立したBooleanストレージクラスもない。真偽値は整数の0と1で保存される。SQLite 3.23.0以降で認識されるFALSEとTRUEは、それぞれ整数リテラル0と1の別表記である。また、SQLite 3.37.0以降には、型を厳格に強制するSTRICTテーブルが用意されている。SQLite公式「Datatypes In SQLite」

入力文字列とタイムゾーンの扱い

文字列を入力する場合にも、日時関数が対応する形式を確認する必要がある。公式には、日付だけのYYYY-MM-DD、日時を含むYYYY-MM-DD HH:MM:SS、小数秒を含む形式、日付と時刻をTで区切る形式などが列挙されている。

時刻だけのHH:MM、HH:MM:SS、HH:MM:SS.SSSも入力できる。この場合、日付には2000-01-01が使われる。ISO-8601には多様な表記があるが、SQLiteが対応するのは公式に列挙された形式である。

対応する日時文字列には、Zまたは±HH:MM形式のタイムゾーン指定を付けられる。日時関数は内部でUTCを使用し、Zは値を変えない。ゼロ以外のオフセットは、指定された日時から差し引いてUTCへ変換される。

公式では、次の値が同じ日時を表す例として掲載されている。

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685

また、'now'は使用中のVFSから取得した現在日時を表す。同じsqlite3_step()の呼び出し内で複数回使われた'now'は、同じ値を返す。timediff()以外の日時関数では、time-valueを省略した場合にも'now'が使われる。SQLite公式「Date And Time Functions」

自分の環境で確かめる手順

sqlite3を実行できる環境では、次のコマンドで数値入力への指定を比較できる。数値と日時文字列には、公式ドキュメントのtime-valueの例を使っている。

sqlite3 :memory: "SELECT datetime(1748528160), datetime(1748528160, 'unixepoch');"
sqlite3 :memory: "SELECT unixepoch('2025-05-29 14:16:00');"

最初のコマンドでは、同じ数値について修飾子の有無を比較する。どちらもdatetime()を使っているため、選んだ関数は同じだが、入力に対する指定が異なる。

次のコマンドでは、日時文字列を入力し、Unixタイムスタンプを返す関数を使っている。入力を文字列で表し、出力を数値として得る例である。

値と一緒に型も確認するには、データ型の公式例で使われているtypeof()を組み合わせられる。

SELECT
  datetime(1748528160, 'unixepoch'),
  typeof(datetime(1748528160, 'unixepoch'));

SELECT
  unixepoch('2025-05-29 14:16:00'),
  typeof(unixepoch('2025-05-29 14:16:00'));

確認する項目は、入力値の形式、修飾子の有無、出力に使う関数、戻り値の型の四つだ。保存時に整数だったという情報だけで判断せず、日時関数に渡すSQL全体で確認する。

日時の差と修飾子の順序

二つの日時について日数や秒数の差を求める方法として、公式ドキュメントにはjulianday()同士、またはunixepoch()同士の引き算が示されている。

次は、その方法に日時文字列を当てはめた例だ。

SELECT julianday('2023-03-15') - julianday('2023-02-15');
SELECT unixepoch('2023-03-15') - unixepoch('2023-02-15');

最初の式は日数、次の式は秒数の差を求める。同じ日時文字列を入力しても、計算に使う関数によって結果の単位が変わる。SQLite公式「Date And Time Functions」

日時を移動させる場合には、修飾子の順序も関係する。timediff()以外の日時関数は、time-valueの後ろに複数の修飾子を受け取れる。修飾子は左から右へ順に適用される。

公式に挙げられている修飾子には、NNN days、NNN hours、NNN months、NNN yearsなどがある。start of month、start of year、start of dayは、それぞれ対象の月、年、日の始まりへ日時を戻す。

月や年の移動には、移動先の日付が一意に決まらない場合がある。公式では、2024-02-29の1年後や、2023-12-31の2か月後が例に挙げられている。この曖昧さを指定する修飾子がceilingとfloorである。

時間移動の直後にceilingを置くと、曖昧さがある場合に遅い方の日付を選ぶ。floorは前の月の最終日を選び、既定の動作はceilingとなる。SQLite公式「Date And Time Functions」

日時を扱うSQLでは、数値の単位、入力形式を指定する修飾子、結果を返す関数に加え、日時を移動させる修飾子の並びも確認したい。保存形式と入力の解釈を対応させ、必要な単位で結果を得ることが、SQLiteの日時関数を読む際の基本になる。

コメント

タイトルとURLをコピーしました