2022年1月22日土曜日

SQLite3では日時は文字列型で保存する

概要

SQLite3のカラムのデータ型にはDate型やDatetime型が無いので、 データベースには文字列型として登録する。

日付の大小を比較するのであれば文字列のまま比較すればいいが、 日付の計算をしたい場合はdate()やdatetime()といった関数を使用する。

実行例

環境
  • Windows 10
  • sqlite3 (3.27.0)

現在日時をデータベースに登録する

  1. 日付を登録するカラムは「text」型にしておく。
  2. 本日日付を「date('now')」で取得し、新規挿入する。
  3. 現在時間を「time('now')」で取得し、新規挿入する。
  4. 現在日付、時間を「datetime('now')」で取得し、新規挿入する。
  5. selectで現在日時が登録されていることが確認できる。

sqlite> -- # 1.
sqlite> create table sample_date (
   ...>     test_date text
   ...> );

sqlite> -- # 2.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     date('now')
   ...> );

sqlite> -- # 3.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     time('now')
   ...> );

sqlite> -- # 4.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     datetime('now')
   ...> );

sqlite> -- # 5.
sqlite> select test_date from sample_date;
2022-01-18
13:47:39
2022-01-18 13:47:40

特定の日付を登録する

文字列として登録すればいいのだが、後で「date()」「datetime()」で使用するために SQLite3の日時フォーマットにしたがった文字列にしたほうがいい。

日時フォーマットについては以下の記事を参照のこと。

スラッシュ区切りなんかで登録してしまうと後々面倒なことになる。

以下は特定の日時を登録する実行例。

  1. 日付を登録するカラムは「text」型にしておく。
  2. 「2022年1月1日」を新規挿入する。
  3. 「9時9分9秒」を新規挿入する。
  4. 「2022年1月1日9時9分9秒」を新規挿入する。

sqlite> -- # 1.
sqlite> create table sample_date (
   ...>     test_date text
   ...> );

sqlite> -- # 2.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '2022-01-01'
   ...> );

sqlite> -- # 3.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '09:09:09'
   ...> );

sqlite> -- # 4.
sqlite> insert into sample_date (
   ...>     test_date
   ...> )
   ...> values (
   ...>     '2022-01-01 09:09:09'
   ...> );

sqlite> -- # 5.
sqlite> select test_date from sample_date;
2022-01-01
09:09:09
2022-01-01 09:09:09

日時を比較する

日時を比較する場合、フォーマットがあっていれば文字列として比較することもできる。

万全を期すのであればdate()やdatetime()といった関数で日付型にしてから比較したほうがいい。
「YYYY-MM-DD HH:MM:SS」と「YYYY-MM-DDTHH:MM:SS」といったフォーマットが違う日付データを 文字列として比較すると、意図しない結果になることがある。

以下は日時比較の実行例

  1. フォーマットが同じなら文字列として比較出来る。
  2. date()やdatetime()関数で変換してからも比較出来る。
  3. フォーマットが違う場合はdate()やdateitme()関数を使うこと。

sqlite> -- # 1.
sqlite> select '2022-01-01 09:00:00' > '2022-01-01 08:59:59';
1
sqlite> select '2022-01-01 09:00:00' > '2022-01-01 10:01:01';
0
sqlite> select '2022-01-01 09:00:00' = '2022-01-01 09:00:00';
1

sqlite> -- # 2.
sqlite> select datetime('2022-01-01 09:00:00') > datetime('2022-01-01 08:59:59');
1

sqlite> -- # 3.
sqlite> select '2022-01-01 09:00:00' > '2022-01-01T08:59:59';
0
sqlite> select datetime('2022-01-01 09:00:00') > datetime('2022-01-01T08:59:59');
1

外部参考サイト

Posted by at
Labels: , ,