SQLiteでの曜日計算
SQLiteでの曜日計算は、strftime(format, timestring, modifier, modifier, ...)関数で、format に %w を指定することで、日曜0開始の数値を得ることで行う。
SQLiteでの連続日付view作成=カレンダーテーブルでも、いくつか用法を紹介したが、こちらのページでは、曜日関連の問題を扱ってみます
参考:日付関数の使い方 Modifiers(修飾子)の指定, strftime関数、SQLite.org Document: datetime function
以下は、SQLite 3.8 で検証しています
SQLiteでの連続日付view作成=カレンダーテーブルでも、いくつか用法を紹介したが、こちらのページでは、曜日関連の問題を扱ってみます
参考:日付関数の使い方 Modifiers(修飾子)の指定, strftime関数、SQLite.org Document: datetime function
以下は、SQLite 3.8 で検証しています
まずは本日の曜日
SELECT date('now', 'localtime') as today, strftime("%w", 'now', 'localtime') as weeknumber , substr('日月火水木金土', strftime("%w", 'now', 'localtime')+1, 1) as weekname ; -- cakephp で実行するには、from 句がないと、うまくデータ取得できないので、サブクエリでテーブルを作る SELECT "Dum".today, strftime("%w",today) as "weeknumber" , substr('日月火水木金土', strftime("%w",today)+1, 1) as "weekname" from (select date('now', 'localtime') as today) as "Dum" ;
SQLite文法メモ1: 現在日時の 'now' には 'localtime' も付けないと、グリニッジ時刻(9時間ずれる)表示になってしまいます。
SQLite文法メモ2: substr の開始位置は1番開始で指定、文字数は、多バイト文字は utf8 での文字数となります。取得例 today weeknumber weekname 2023-12-09 6 土 日付カラムから曜日算出
table名: test_tbl , 日付カラム: test_date DATE CHECK( test_date like '____-__-__') の書式とする。
曜日名は英字3文字略語で得るようにしてみた。SELECT test_date, strftime("%w", test_date) as weeknumber , substr('SunMonTueWedThuFriSat', strftime("%w", test_date)*3 +1, 3) as weekname from test_tbl
取得例 test_date weeknumber weekname 2016-12-31 6 Sat 2017-01-01 0 Sun 2017-02-28 2 Tue 2017-03-03 5 Fri 2017-05-31 3 Wed ある期間に特定曜日が含まれるか
期間が7日以上あれば、すべての曜日が含まれることは明かだが、7日未満の時は、含まれるかどうかのチェックが居る。
そこで、SQLiteの日時関数では、Modifiers に 'weekday N' の書式で、指定日以降の該当曜日(指定日含む)の日付を得ることができるので、開始日付以降の該当曜日日付を算出して、終了日付と比較すればよい。
table名: test_tbl , 日付カラム: start_date , end_date :いずれも DATE CHECK( start_date like '____-__-__') の書式とする
以下は、'weekday 0' で日曜日の日付を得るSELECT start_date , date(start_date,'weekday 0') as nextsun, end_date , case when ( julianday(end_date) - julianday(start_date) ) >= 7 then '全て含む' when date(start_date, 'weekday 0') <= end_date then '日曜含む' else '含まない' end as judgment from test_tbl
ある期間に特定曜日が何回含まれるか
開始日付以降の該当曜日(当日含む)日付を起点とし、終了日前の該当曜日日付までの日数(両端含むのでjuliandayの差に+1) を7で割り商に1を足し、 終了日が該当曜日の場合は前週までの計算になるので、さらに1を足すと 「開始日と終了日の曜日も含んだ特定曜日の回数」が得られる。 (終了日前の該当曜日が開始日前となっても7日未満のはずなので、7で割った商は-1、これに+1で結果は0日とでる)
以下は各曜日をサブクエリテーブルで与えて全曜日でチェックする場合。 wcount が求める曜日回数SELECT start_date, date( start_date, 'weekday ' || w.wn) as startweekday , end_date , date( end_date, 'weekday ' || w.wn, '-7 days') as endweekday , round( (julianday(end_date,'weekday '||w.wn,'-7 days') - julianday(start_date,'weekday '||w.wn) +1) / 7 ) + 1 + (case when strftime("%w", end_date) = w.wn then 1 else 0 end) as wcount , w.name weekname from test_tbl , (select '0' as wn, 'Sun' name union select '1','Mon' union select '2','Tue' union select '3','Wed' union select '4','Thu' union select '5','Fri' union select '6','Sat' ) as w order by test_tbl.start_date , w.wn
SQLite文法メモ1: || で文字列連結する。
SQLite文法メモ2: strftimeの返値は文字列型なので、曜日番号も文字列として与えておかないと正しく比較できない。 サブクエリテーブルではカラム型が規定されないためらしい?
SQLite文法メモ3: round(x): 小数点以下切り捨て , round(x,n): 第2引数で小数点第n位切り捨てを指定できる。整数に切り捨てられても浮動小数点表現で返ってくる。取得例 start_date startweekday end_date endweekday wcount weekname 2017-05-01 2017-05-07 2017-05-31 2017-05-28 4.0 Sun 2017-05-01 2017-05-01 2017-05-31 2017-05-29 5.0 Mon 2017-05-01 2017-05-02 2017-05-31 2017-05-30 5.0 Tue 2017-05-01 2017-05-03 2017-05-31 2017-05-24 5.0 Wed 2017-05-01 2017-05-04 2017-05-31 2017-05-25 4.0 Thu 2017-05-01 2017-05-05 2017-05-31 2017-05-26 4.0 Fri 2017-05-01 2017-05-06 2017-05-31 2017-05-27 4.0 Sat 2017-05-17 2017-05-21 2017-05-20 2017-05-14 0.0 Sun 2017-05-17 2017-05-22 2017-05-20 2017-05-15 0.0 Mon 2017-05-17 2017-05-23 2017-05-20 2017-05-16 0.0 Tue 2017-05-17 2017-05-17 2017-05-20 2017-05-17 1.0 Wed 2017-05-17 2017-05-18 2017-05-20 2017-05-18 1.0 Thu 2017-05-17 2017-05-19 2017-05-20 2017-05-19 1.0 Fri 2017-05-17 2017-05-20 2017-05-20 2017-05-13 1.0 Sat
曜日テーブルを使って、曜日名を得る
曜日番号から曜日名を得るには、最初の本日の曜日で紹介した substr() を使う方法では、文字数が各曜日で同じでないとうまくいきません。 また、期間内の特定曜日カウントでは、サブクエリで曜日番号と曜日名を与えましたが、これを先に実テーブルに作成してしまえば、英語曜日名や日本語曜日名もカラム指定を変更するだけで簡単に取れるようになります。-- 1. 曜日名格納テーブル作成(id:曜日番号用0開始6まで;en_full:英文フルスペル名;en3s:英文3文字略記名;jp1:日本語1文字表記) create table weekname ( id INTEGER primary key not null , en_full TEXT , en3s TEXT , jp1 TEXT ); insert into weekname values ( 0, 'Sunday' , 'Sun' , '日' ); insert into weekname values ( 1, 'Monday' , 'Mon' , '月' ); insert into weekname values ( 2, 'Tuesday' , 'Tue' , '火' ); insert into weekname values ( 3, 'Wednesday', 'Wed' , '水' ); insert into weekname values ( 4, 'Thursday' , 'Thu' , '木' ); insert into weekname values ( 5, 'Friday' , 'Fri' , '金' ); insert into weekname values ( 6, 'Saturday ', 'Sat' , '土' ); -- 本日の曜日、英文フルスペル、英文3文字略記名、日本語1文字表記 SELECT Dum.today, weekname.id, weekname.en_full , weekname.en3s , weekname.jp1 from weekname inner join (select date('now', 'localtime') as today ) as "Dum" on weekname.id = strftime("%w", Dum.today ) ;
SQLite文法メモ1:こちらでは、weekname.id がintegerと確定しているので、自動変換してstrftimeの返値との比較は適切に行われる。取得例 Dum.today weekname.id weekname.en_full weekname.en3s weekname.jp1 2023-12-09 6 Saturday Sat 土
[最終更新日 ]