SQLiteを学んでいこうという連載の8回目です。前回の記事はこちらになります。
前回は、SQLiteでの日付や時間のデータの扱いについてご紹介しました。SQLiteでは、日時のデータを決まったフォーマットの文字列型で管理しています。
このフォーマットの文字列が日付や時刻を表すわけですが、異なる形で日時のデータを扱いたい場合もあります。今回は日時データの書式変更についてご紹介しようと思います。
strftime()
strftime() 関数は、書式と日時を表すデータから指定の書式の日時(文字列データ)を返します。記述方法は以下の通りです。
strftime(書式,日時データ)
指定できる書式は以下になります。
書式 | 返り値 |
---|---|
%Y | 年: 0000-9999 |
%m | 月: 01-12 |
%d | 日: 01-31 |
%W | 年初からの週数: 00-53 |% |
%j | 年初からの日数: 001-366 |
%w | 曜日:(日:0,月:1,火:2,水:3,木:4,金:5,土:6) |
%H | 時: 00-24 |
%M | 分: 00-59 |
%S | 秒: 00-59 |
%f | 秒+ミリ秒: SS.SSS |
%s | 1970-01-01からの秒数 |
%J | ユリウス日 |
%% | % |
日時データは前回ご紹介した書式の文字データ以外に、CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP、'now'も使用できます。まず簡単な使用例として、以下が挙げられます。
sqlite> SELECT strftime('%Y年%m月%d日','now'); 2023年01月17日
この様に日付の表示形式を変更できたりします。他にも応用方法を考えてみました。
曜日データを選択する
書式で「%w」を使用すると、該当する日時の曜日が取得できます。たとえば以下のようなテーブルがあったとします。
sqlite> CREATE TABLE sample(id INTEGER,date TEXT); sqlite> INSERT INTO sample (id ,date) ...> VALUES(1,"2023-01-10"), ...> (2,"2023-01-11"), ...> (3,"2023-01-12"), ...> (4,"2023-01-13"), ...> (5,"2023-01-14"), ...> (6,"2023-01-15"), ...> (7,"2023-01-16");
IDと日付(のテキストデータ)からなるテーブルです。ここから水曜日のデータだけを抽出したい場合は以下で可能です。
sqlite> SELECT * FROM sample WHERE strftime('%w',date) = '3'; ┌────┬──────────────┐ │ id │ date │ ├────┼──────────────┤ │ 2 │ '2023-01-11' │ └────┴──────────────┘
水曜日のデータが選択されました。strftime()の返り値が文字列型であるとに注意してください。
誕生日から年齢を算出する
誕生日のデータから任意の日付での年齢を算出してみましょう。以下のテーブルを作成しました。
sqlite> CREATE TABLE sample(id INTEGER,name TEXT,birthday TEXT); sqlite> INSERT INTO sample (id ,name ,birthday) ...> VALUES(1,"YAMADA","1990-02-21"), ...> (2,"SUZUKI","1997-05-19"), ...> (3,"TAKASHIRO","1979-04-24");
ここから2023年2月20日時点でのメンバーの年齢を求めてみましょう。
sqlite> SELECT name, ...> strftime('%Y','2023-02-20') - strftime('%Y',birthday) ...> - (strftime('%m%d','2023-02-20') < strftime('%m%d',birthday)) as age ...> FROM sample; ┌─────────────┬─────┐ │ name │ age │ ├─────────────┼─────┤ │ 'YAMADA' │ 32 │ │ 'SUZUKI' │ 25 │ │ 'TAKASHIRO' │ 43 │ └─────────────┴─────┘
結果は以下の様になり、年齢を算出できました。2つの日時の年の部分を引けば、年齢の概算ができます(strftime()の返り値は先にも述べた通り文字列ですが、引き算の過程で数値型に変換されています)。しかしながら実際の年齢は誕生日を境に加算されるので、この部分を加味しなければなりません。
SQLiteでは判定式の結果がTrue(真)の場合は数値の「1」、False(偽)の場合は数値の「0」を返しますのでこれを利用しましょう。指定日時の月日が誕生日の月日より前の場合は上記の値から1を引きます。
上の例ではYAMADAさんの年齢が32歳となっていました。2月20日は誕生日の前日です。1日後にはどうなっているでしょうか?
sqlite> SELECT name, ...> strftime('%Y','2023-02-21') - strftime('%Y',birthday) ...> - (strftime('%m%d','2023-02-21') < strftime('%m%d',birthday)) as age ...> FROM sample; ┌─────────────┬─────┐ │ name │ age │ ├─────────────┼─────┤ │ 'YAMADA' │ 33 │ │ 'SUZUKI' │ 25 │ │ 'TAKASHIRO' │ 43 │ └─────────────┴─────┘
YAMADAさんの年齢が33歳となりました。 strftime()を使用すると、日付、時間のデータを色々な形で利用できますね。
次回はその他の注意事項
いかがでしょうか?SQLiteは日時のデータが文字列型なのでstrftime()関数を如何に上手く使うかがカギになります。次回は日時のデータを扱うにあたっての、その他の注意事項です。お楽しみに!!!