日時データのポイント（SQLiteを学ぶ_9）

SQLiteを学んでいこうという連載の9回目です。前回の記事はこちらになります。

hirocom777.hatenadiary.org

前回は、SQLiteでの日時データの書式変更についてご紹介しました。strftime()関数を使用することで色々な形で日時の表記をできます。

今回はSQLiteで日時データを扱う際のポイントです。これまでお伝えしてきた通り、SQLiteの日時データは結構癖があります。

文字データである

SQLiteの日時データは文字データです。日時データのを比較する際は、フォーマットを統一していないと間違った結果になります。数値として正確に比較したい場合は、strftime() 関数で書式を「%s」に指定して日時データを変換しましょう。1970-01-01からの秒数に変換されるため、正確に比較できます。

また、strftime() 関数で曜日を取得する際に返ってくる値も0~6の数値ではなくて文字データです。参照、確認する際に気を付けましょう。

時間の差を算出する

前回誕生日から現在の年齢を算出する方法をご紹介しましたが、逆に2つの日時データからその差を取得するにはどうしたらいいでしょうか。strftime() 関数で算出できます。単位別にみてみましょう。基準となる日時データ「2022-03-20 10:03:14」から、経過時間を算出する日時データ「2023-01-10 08:45:58」の差（経過時間）を算出します。

年

年数の算出は書式を「%Y」と指定して引き算すれば算出できます。

sqlite> SELECT strftime('%Y','2023-01-10 08:45:58')
   ...> - strftime('%Y','2022-03-20 10:03:14');
1

月

月数の算出は、上で算出した値に12を掛けた結果からから基準となる日時データの月を引いたうえで、経過時間を算出する日時データの月を足します。月は書式「%m」で取得します。

sqlite> SELECT (strftime('%Y','2023-01-10 08:45:58')
   ...> - strftime('%Y','2022-03-20 10:03:14')) * 12
   ...> - strftime('%m','2022-03-20 10:03:14')
   ...> + strftime('%m','2023-01-10 08:45:58');
10

日

日数の算出は、書式「%J」を使って算出しましょう。書式「%J」はユリウス日（紀元前4714年11月24日正午からの経過日数）を返します。date()関数を使って書式を「YYYY-MM-DD」の形にしてから算出しましょう。

sqlite> SELECT strftime('%J',date('2023-01-10 08:45:58'))
   ...> - strftime('%J',date('2022-03-20 10:03:14'));
296.0

時間、分、秒

時間、分、秒の差は書式「%s」を使って算出しましょう。書式「%s」は1970-01-01からの経過秒数を返します。経過時間を算出する日時データの秒数から基準となる日時データの秒数を引いた値になります。秒以外は単位総統の秒数（時間：3600.0、分：60.0)で割って求めます。結果は必要に応じて切り上げ、切り捨ての処理をしましょう。

sqlite> SELECT (strftime('%s','2023-01-10 08:45:58')
   ...> - strftime('%s','2022-03-20 10:03:14')) / 3600.0;
7102.71222222222
sqlite> SELECT (strftime('%s','2023-01-10 08:45:58')
   ...> - strftime('%s','2022-03-20 10:03:14')) / 60.0;
426162.733333333
sqlite> SELECT (strftime('%s','2023-01-10 08:45:58')
   ...> - strftime('%s','2022-03-20 10:03:14'));
25569764

UTCとJST

SQLiteで適用されている時間はUTC（協定世界時）です。我々が普段使用しているJST（日本時間）は協定世界時より9時間進んでいます。datetime()関数で修飾子に「'utc'」を使用すると現地時間で指定した日時データをUTCに変換します。また「'localtime'」を指定するとUTCで指定された日時データを現地時間に変換します。日本で「2023-01-19 15:53:32」の時点で以下を実行して結果を見てみました。

sqlite> SELECT datetime('now');
2023-01-19 06:53:32
sqlite> SELECT datetime('now','utc');
2023-01-18 21:53:32
sqlite> SELECT datetime('now','localtime');
2023-01-19 15:53:32

SQLiteで異なる時間設定でデータベースを共有することはないと思いますが、どの時間帯で日時データを管理しているかは常に注意しなければなりません。