2021年12月18日土曜日

SQLite3で年月日の計算をする

概要

SQLiteで日付の計算をするには「date()」、もしくは「datetime()」を使用する。

構文


date(time-value, modifier, modifier, ...)
datetime(time-value, modifier, modifier, ...)

実行例

環境
  • Windows 10 64bit
  • SQLite3 ver.3.36.0

年の計算

「date()」や「datetime()」の引数に「NNN years」を追加する。
例えば１年後を計算したければ「+1 years」を追加する。 


sqlite> select date('2021-01-01', '+1 years');
2022-01-01

sqlite> select date('2021-01-01', '-1 years');
2020-01-01

sqlite> select datetime('2021-01-01 09:00:00', '+1 years');
2022-01-01 09:00:00

sqlite> select datetime('2021-01-01 09:00:00', '-1 years');
2020-01-01 09:00:00
「0000年」や「9999年」を計算すると？
「0000年」からさらに年数を引くと紀元前の文字列が取得出来る。

通常「date()」の計算結果はさらに「date()」の引数として指定することが出来るが、 0000年からさらに年数を引いた計算結果を「date()」に入れると「null」になってしまう。

紀元前の計算をしたいのであればユリウス日を使うようにする。 


sqlite> .nullvalue [null]
sqlite> select date('0000-01-01', '-1 years');
-001-01-01

sqlite> select date(date('0000-01-01', '-1 years'), '-1 years');
[null]

sqlite> select date(date('0003-01-01', '-1 years'), '-1 years');
0001-01-01
「9999年」にさらに年数を足すことは出来ない

「9999年」にさらに年数を足しても結果は必ず「null」になる。 


sqlite> .nullvalue [null]
sqlite> select date('9999-01-01', '+1 years');
[null]

月の計算

「date()」や「datetime()」の引数に「NNN months」を追加する。
例えば１か月後を計算したければ「+1 months」を追加する。

年を跨ぐ場合もちゃんと計算される。 


sqlite> select date('2021-01-01', '+1 months');
2021-02-01

sqlite> select date('2021-01-01', '-1 months');
2020-12-01

sqlite> select date('2021-12-01', '+1 months');
2022-01-01

sqlite> select date('2021-01-01', '+13 month');
2022-02-01

sqlite> select datetime('2021-01-01 09:00:00', '+1 months');
2021-02-01 09:00:00

sqlite> select datetime('2021-12-01 09:00:00', '-1 months');
2021-11-01 09:00:00

日の計算

「date()」や「datetime()」の引数に「NNN day」を追加する。
例えば１日後を計算したければ「+1 day」を追加する。

年や月を跨ぐ場合もちゃんと計算される。 


sqlite> select date('2021-01-01', '+1 day');
2021-01-02

sqlite> select date('2021-01-01', '-1 day');
2020-12-31

sqlite> select date('2021-12-31', '+1 day');
2022-01-01

sqlite> select date('2021-01-01', '+366 day');
2022-01-02

sqlite> select datetime('2021-01-01 09:00:00', '+1 day');
2021-01-02 09:00:00

sqlite> select datetime('2021-01-01 09:00:00', '-1 day');
2020-12-31 09:00:00

selectの結果で日付を計算する

上記の通り、日付を計算するには「date()」や「datetime()」の引数に文字列を指定する必要がある。 データベース内のデータで計算する場合、文字列を連結して引数を作ってやればいい。

他のテーブルのデータを利用する場合はテーブルを結合する。

以下は「num」カラムの値を「date」カラムの日に足す例。

date_list テーブル
str_date
text 		int_num
integer
2021-01-01 1
2021-01-01 2
2021-01-01 3 

sqlite> select
   ...>     date(
   ...>         str_date
   ...>         , '+' || int_num || ' day'
   ...>     )
   ...> from
   ...>     date_list
   ...> ;
2021-01-02
2021-01-03
2021-01-04

外部参考サイト

Posted by at
Labels: ,