SQLite 3 で年数単位の計算をする

概要

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
                        

年を加算/減算する

上記の式で納得できるのであれば、それでOK。

ただ、コードの保守性や引継ぎを考えると 「ここは年の計算をしている」ことがコードからわかることがベスト。

年単位の加算/減算をする場合はdate()や datetime()の引数にNNN yearsを追加する。 例えば１年後を計算したければ+1 yearsを追加する。

sqlite> select date('2026-04-01', '+1 years');
2027-04-01

sqlite> select date('2026-04-01', '-1 years');
2025-04-01

sqlite> select datetime('2026-04-01 09:00:00', '+1 years');
2027-04-01 09:00:00

sqlite> select datetime('2026-04-01 09:00:00', '-1 years');
2025-04-01 09:00:00
                        

例えばこのようなテーブルの各行の１年後、１年前を計算するには以下のようなSQLになる。

user_table

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 years') as next_year
    , date(expiration_date, '-1 years') as prev_year
from
    user_table
;
                        

結果

user_name	expiration_date	next_year	prev_year
hoge1	2026-04-15	2027-04-15	2025-04-15
hoge2	2026-12-31	2027-12-31	2025-12-31
hoge3	2024-02-29	2023-03-01	2025-03-01

２つの日付間の年数を計算する

大きく分けて2パターン。

１つ目は、strftime()関数を使って年の部分だけを取り出して計算する方法。
２つ目は、julianday()関数を使って日数に変換してから年数に変換する方法。

１つ目の方法は、単純に年の部分だけを取り出して計算する方法。
例えば以下のようなSQLになる。

sqlite> select strftime('%Y', '2026-05-01') - strftime('%Y', '2025-11-01') as span_year;
1
                        

ただし、この方法は単純に年の部分だけを取り出して計算するため、月や日が考慮されない。
例えば上記のSQLでは、2026年5月1日と2025年11月1日の年数の差は1年と計算されるが、実際には6ヶ月の差しかない。

２つ目の方法は、日付を日数に変換してから年数に変換する方法。
例えば以下のようなSQLになる。

sqlite> select (julianday('2026-05-01') - julianday('2026-03-01')) / 365.25;
0.167008898015058
                        

この方法では、日付を日数に変換してから年数に変換するため、月や日が考慮される。
例えば上記のSQLでは、2026年5月1日と2026年3月1日の年数の差は0.16年と計算される。 （厳密にうるう年を計算しようと思うと大変なのでここでは0.25日を使う。）
必要に応じて小数点以下を切り捨てるなどの処理を行うと良い。

sqlite> select cast((julianday('2026-05-01') - julianday('2026-03-01')) / 365.25 as int);
0
sqlite> select cast((julianday('2026-05-31') - julianday('2025-04-30')) / 365.25 as int);
1
                        

• SQLite 3 で二つの日付間の日数を計算する

参考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

SQLite 3 年・月・日を取得する（strftime()）

概要

日付から年・月・日を個別に取得するには、strftime()関数を使用する。

実行例

環境

• Windows 11
• sqlite Command-Line Shell ver 3.51.3

年を所得する。

年を取得するにはstrftime('%Y', <日付>)を使用する

sqlite> select strftime('%Y', '2026-01-31');
2026
                        

月を取得する

月を取得するにはstrftime('%m', <日付>)を使用する

sqlite> select strftime('%m', '2026-01-31');
01
                        

日を取得する

日を取得するにはstrftime('%d', <日付>)を使用する

sqlite> select strftime('%d', '2026-01-31');
31
                        

参考URL

• SQLite Query Language: Date And Time Functions(英語)
  公式の日時型に関するドキュメント
  https://www.sqlite.org/lang_datefunc.html
• Date And Time Functions(英語)
  公式のstrftime()関数の書式指定子に関するドキュメント
  https://www.sqlite.org/lang_datefunc.html#strftime
• SQLite 備忘録: SQLite3 年内日を求める(sfrftime('%j','yyyy-MM-dd'))
  https://sfnovicenotes.blogspot.com/2022/02/sqlite3-sfrftimejyyyy-mm-dd.html
• SQLite 備忘録: SQLite3 日付から曜日を取得する (strftime('%w', 'yyyy-MM-dd'))
  https://sfnovicenotes.blogspot.com/2022/02/sqlite3-strftimew-yyyy-mm-dd.html
• SQLite 備忘録: SQLite3 年内週を取得する (strftime('%W', 'yyyy-MM-dd'))
  https://sfnovicenotes.blogspot.com/2022/02/sqlite3-strftimew-yyyy-mm-dd_0360293618.html

SQLite 3 .mode で幅広文字の表示が改善された

概要

コマンドラインツールには select 文の結果表示モードを変更する .mode コマンドがある。

• SQLite3 .mode コマンドの出力サンプル

今までだと、このコマンドで指定できるモードのうち box や column では 幅広の文字を表示しようとすると体裁が崩れてしまっていた。

ver 3.51.0 でこれが改善され、日本語のひらがなや漢字でも体裁が崩れることなく表示されるようになった。

構文

改善された表示モードは以下の通り。

.mode box
.mode column
.mode markdown
.mode qbox
.mode table
                    

実行例

まずは古いバージョンでの見え方。

環境

• Windows 11
• SQLite3 Command-Line Shell ver 3.49.0

sqlite> .version
SQLite 3.49.0 2025-02-06 11:55:18 4a7dd425dc2a0e5082a9049c9b4a9d4f199a71583d014c24b4cfe276c5a77cde
zlib version 1.3
msvc-1939 (64-bit)

sqlite> create table user (id, name);
sqlite> insert into user values ('0001', 'ほげほげ');
sqlite> select * from user;
0001|ほげほげ

sqlite> .mode box
sqlite> select * from user;
┌──────┬──────┐
│  id  │ name │
├──────┼──────┤
│ 0001 │ ほげ │
└──────┴──────┘

sqlite> .mode column
sqlite> select * from user;
id    name
----  ----
0001  ほげ

sqlite> .mode markdown
sqlite> select * from user;
|  id  | name |
|------|------|
| 0001 | ほげ |

sqlite> .mode qbox
sqlite> select * from user;
┌────────┬────────┐
│   id   │  name  │
├────────┼────────┤
│ '0001' │ 'ほげほげ'  │
└────────┴────────┘

sqlite> .mode table
sqlite> select * from user;
+------+------+
|  id  | name |
+------+------+
| 0001 | ほげ |
+------+------+
                        

上記の例では、「name」カラムに「ほげほげ」というデータが入っているが、 表示モードを切り替えることで体裁が崩れ、データが欠けてしまっていることがわかる。

これが ver 3.51.0 以降は改善され、表示されるようになる。

環境

• Windows 11 64bit
• SQLite Command-Line Shell ver 3.51.3

sqlite> .version
SQLite 3.51.3 2026-03-13 10:38:09 737ae4a34738ffa0c3ff7f9bb18df914dd1cad163f28fd6b6e114a344fe6d618
zlib version 1.3
msvc-1939 (64-bit)

sqlite> create table user (id, name);
sqlite> insert into user values ('0001', 'ほげほげ');
sqlite> select * from user;
0001|ほげほげ

sqlite> .mode box
sqlite> select * from user;
┌──────┬──────────┐
│  id  │   name   │
├──────┼──────────┤
│ 0001 │ ほげほげ │
└──────┴──────────┘

sqlite> .mode column
sqlite> select * from user;
id    name
----  --------
0001  ほげほげ

sqlite> .mode qbox
sqlite> select * from user;
┌────────┬────────────┐
│   id   │    name    │
├────────┼────────────┤
│ '0001' │ 'ほげほげ' │
└────────┴────────────┘

sqlite> .mode markdown
sqlite> select * from user;
|  id  |   name   |
|------|----------|
| 0001 | ほげほげ |


sqlite> .mode table
sqlite> select * from user;
+------+----------+
|  id  |   name   |
+------+----------+
| 0001 | ほげほげ |
+------+----------+
                        

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html
• Changing Output Formats
  公式の出力モードに関するドキュメント
  https://www.sqlite.org/cli.html#dotmode

• SQLite備忘録: SQLite3 .mode コマンドの出力サンプル
  https://sfnovicenotes.blogspot.com/2021/04/sqlite-3-mode.html

SQLite3 SQL実行にかかった時間を計測する (.timer)

概要

SQLite3 で SQL を実行した際、実行にかかった時間を計測するためのコマンド。

計測出来るのは SQL のみで、コマンドラインツールのコマンドの実行時間は計測出来ない。

構文

時間計測を有効化する「on」/ 無効化する「off」
出力される時間の単位は「秒」。

.timer on
.timer off
                    

実行例

環境

• Windows 11
• SQLite Command-Line Shell ver 3.51.3

時間計測を「on」にしてSQLを実行する

c:\temp> rem  # 1
c:\temp> sqlite3 sample.db

sqlite> -- # 2
sqlite> .timer on

sqlite> -- # 3
sqlite> select * from user;
...
Run Time: real 4.729 user 0.312500 sys 1.078125
                        

1. コマンドラインツールで「sample.db」というsqliteデータベースを開く
2. 時間計測を「on」にする
3. SQLを実行すると、SQLの実行結果の後に計測結果が秒単位で出力される

各項目の意味は以下の表の通りで、これはUNIXの timeコマンドの結果と同等の内容と思われる。 （SQLite3 公式サイトに正式な説明が載っていないので推測）

種類	意味
real	実際に経過した時間。 I/O待ちや他のプロセスの影響などクエリの開始から終了までの時間になる。 コマンドラインツール上であれば、select の結果をディスプレイに出力するのにかかった時間も含まれる。
user	CPUが使われた時間。 実行する SQLの解析、テーブルのJOIN、ソート計算や関数の実行にかかった時間。 SQL 文の品質を確認したいならこの時間を確認すればよさそう。
sys	OSが処理した時間。 ファイルの読み書きの時間が含まれる。 ケースに寄りけりだが、SQLite3 ではそこまで重要視する時間ではないかも。

一般的に real ≧ user + sys となる想定

時間計測を止めるには以下のようにコマンドを実行する

sqlite> .timer off
                        

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html