概要
SQLite にはデータ型として日付型がないため、日付の計算は少し特殊な書き方をする必要がある。
この記事では「月」単位の計算についてまとめる。
実行例
環境
- Windows 10
- sqlite Command-Line Shell ver 3.51.3
単純な日付型の加減算では計算出来ない
date()や
datetime()の加減算では計算出来ず、以下のような結果になってしまう。
どうやら最初の「年」の箇所だけを数値として取り出して計算しているため、このままでは月単位の計算はできない。
sqlite> select date('2026-05-01') - date('2026-03-01');
0
sqlite> select date('2025-01-31') - date('2022-12-31');
3
sqlite> select date('2026-05-01') - 1;
2025
月を加算/減算する
月単位の加算/減算をする場合はdate()や
datetime()の引数にNNN monthsを追加する。
例えば１年後を計算したければ+1 monthsを追加する。
sqlite> select date('2026-04-01', '+1 months');
2026-05-01
sqlite> select date('2026-04-01', '-1 months');
2026-03-01
sqlite> select datetime('2026-04-01 09:00:00', '+1 months');
2026-05-01 09:00:00
sqlite> select datetime('2026-04-01 09:00:00', '-1 months');
2026-03-01 09:00:00
例えばこのようなテーブルの各行の１か月前、１か月後を計算するには以下のようなSQLになる。
| user_name | expiration_date |
|---|---|
| hoge1 | 2026-04-15 |
| hoge2 | 2026-12-31 |
| hoge3 | 2024-02-29 |
select
user_name
, expiration_date
, date(expiration_date, '+1 months') as next_month
, date(expiration_date, '-1 months') as prev_month
from
user_table
;
| user_name | expiration_date | next_month | prev_month |
|---|---|---|---|
| hoge1 | 2026-04-15 | 2026-05-15 | 2026-03-15 |
| hoge2 | 2026-05-31 | 2026-07-01 | 2026-05-01 |
| hoge3 | 2024-02-29 | 2024-03-29 | 2024-01-29 |
5月31日に +1か月 した場合、6月31日は存在しないため、オーバーフローして7月1日が返されるっぽい。
1か月前も同じような理屈になり、4月30日が返されるわけではないので注意が必要。
２つの日付間の月数を計算する
大きく分けて2パターン。
１つ目は、strftime()関数を使って年と月の部分を取り出して計算する方法。
２つ目は、julianday()関数を使って日数に変換してから月数に変換する方法。
１つ目の方法は、単純に年と月の部分を取り出して計算する方法。
年部分の差に12（か月）をかけ、月の差を足せばいい。
sqlite> select
...> ( strftime('%Y', '2026-01-05') - strftime('%Y', '2025-12-30') ) * 12
...> + ( strftime('%m', '2026-01-05') - strftime('%m', '2025-12-30') )
...> ;
1
ただし、この方法は単純に年の部分だけを取り出して計算するため、日数が考慮されない。
例えば上記のSQLでは、2026年1月5日と2025年12月30日の差は1か月と計算されるが、実際には6日の差しかない。
２つ目の方法は、日付を日数に変換してから月数に変換する方法。
例えば以下のようなSQLになる。
sqlite> select (julianday('2026-01-05') - julianday('2025-12-30')) / 30;
0.2
この方法では、日付を日数に変換してから月数に変換するため、日数が考慮される。
例えば上記のSQLでは、2026年1月5日と2025年12月30日の年数の差は0.2か月と計算される。
（厳密に31日の月や2月の日数を計算しようとするとさすがに面倒なので、ここでは1か月=30日で計算している）
必要に応じて小数点以下を切り捨てるなどの処理を行うと良い。
sqlite> select cast((julianday('2026-01-05') - julianday('2025-12-30')) / 30 as int);
0
参考URL
-
SQLite Query Language: Date And Time Functions(英語)
公式の日時型に関するドキュメント
https://www.sqlite.org/lang_datefunc.html -
SQLite備忘録：日時の処理のまとめ
https://sfnovicenotes.blogspot.com/2022/01/sqlite3_02064292495.html -
SQLite備忘録: SQLite 3 で日付型の加減算を行う
https://sfnovicenotes.blogspot.com/2021/12/sqlite3_18.html -
SQLite備忘録: SQLite 3 で二つの日付間の日数を計算する
https://sfnovicenotes.blogspot.com/2023/01/sqlite3.html