2026年4月26日日曜日

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
月を加算/減算する

月単位の加算/減算をする場合は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_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 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

Posted by at
Labels: , , ,

2026年4月18日土曜日

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月29日に年単位の加算・減算をした結果、 その年がうるう年でない場合は2月28日ではなく 3月1日になるので注意すること。

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

大きく分けて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

参考URL

Posted by at
Labels: , , ,

2026年3月28日土曜日

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

Posted by at
Labels: , ,

2026年3月21日土曜日

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

概要

コマンドラインツールには select 文の結果表示モードを変更する .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

Posted by at
Labels: , , ,
登録: コメント (Atom)