SQLite 備忘録

2026年6月23日火曜日

SQLite3 指定した文字を挟んで文字列を連結する (concat_ws(separator, X, Y, ...))

概要

文字列を連結（結合）するには||演算子か関数 concat()を使用するが、カンマやドットなどの文字を挟んで連結する場合は concat_ws()を使用する。

ExcelのTEXTJOIN()関数と似たような感覚で使える。

ws は whitespace の略…か？

構文


concat_ws(separator, X, Y, ...)

実行例

以下はカンマを使って文字列を連結する場合。
concat()でも同じ結果が得られるが、 concat_ws()の方が簡単に書ける。


sqlite> select concat_ws(',', 'Japan', 'Tokyo', 'Shibuya');
Japan,Tokyo,Shibuya
sqlite> select concat('Japan', ',', 'Tokyo', ',', 'Shibuya');
Japan,Tokyo,Shibuya

テーブルのカラムを連結する例:


sqlite> .mode box
sqlite> select * from user;
┌────┬────────────┬───────────┐
│ id │ first_name │ last_name │
├────┼────────────┼───────────┤
│ 1  │ taro       │ yamada    │
│ 2  │ jiro       │ yamada    │
│ 3  │ hanako     │ suzuki    │
└────┴────────────┴───────────┘
sqlite> select
   ...>     concat_ws('.', first_name, last_name)
   ...>         as full_name
   ...> from
   ...>     user;
┌───────────────┐
│   full_name   │
├───────────────┤
│ taro.yamada   │
│ jiro.yamada   │
│ hanako.suzuki │
└───────────────┘

SQLite 3 コマンドラインツールでselectの結果を見やすくする

NULLを含む場合

区切り文字にNULLを指定した場合、他の引数の内容にかかわらず、結果はNULLになる。


sqlite> .nullvalue [null]
sqlite> select concat_ws(NULL, 'Japan', 'Tokyo', 'Shibuya');
[null]

区切り文字以外にNULLを指定した場合、NULLは無視され、余分な区切り文字が入ることもない。


sqlite> select concat_ws(',', NULL, 'Tokyo', 'Shibuya');
Tokyo,Shibuya

SQLite 3 コマンドラインツールでnullの表示を見やすくする

参考URL

公式のconcat_ws()に関するドキュメント
https://www.sqlite.org/lang_corefunc.html#concat_ws

SQLite3 文字列を連結する (concat(X,Y,...))
https://sfnovicenotes.blogspot.com/2026/02/sqlite3-concatxy.html

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

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

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

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

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

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

登録: 投稿 (Atom)

2026年6月23日火曜日

SQLite3 指定した文字を挟んで文字列を連結する (concat_ws(separator, X, Y, ...))

概要

構文

実行例

NULLを含む場合

参考URL

2026年4月26日日曜日

SQLite 3 で月単位の計算をする

概要

実行例

環境

単純な日付型の加減算では計算出来ない

月を加算/減算する

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

参考URL

2026年4月18日土曜日

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

概要

実行例

環境

単純な日付型の加減算では計算出来ない

年を加算/減算する

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

参考URL

2026年3月28日土曜日

SQLite 3 指定した日付の年・月・日 部分をを取得する（strftime()）

概要

実行例

環境

年を所得する。

月を取得する

日を取得する

参考URL

ページ

SQLite 3 指定した日付の年・月・日部分をを取得する（strftime()）