SQLiteでの曜日計算

SQLiteでの曜日計算

• まずは本日の曜日

  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文法メモ１： 現在日時の 'now' には 'localtime' も付けないと、グリニッジ時刻（９時間ずれる）表示になってしまいます。
  SQLite文法メモ２： substr の開始位置は１番開始で指定、文字数は、多バイト文字は utf8 での文字数となります。

  取得例

  today	weeknumber	weekname
  2023-12-09	6	土

• 日付カラムから曜日算出

  table名: test_tbl , 日付カラム: test_date DATE CHECK( test_date like '____-__-__') の書式とする。
  曜日名は英字３文字略語で得るようにしてみた。

  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

• ある期間に特定曜日が含まれるか

  期間が７日以上あれば、すべての曜日が含まれることは明かだが、７日未満の時は、含まれるかどうかのチェックが居る。
  そこで、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で割り商に１を足し、 終了日が該当曜日の場合は前週までの計算になるので、さらに１を足すと 「開始日と終了日の曜日も含んだ特定曜日の回数」が得られる。 （終了日前の該当曜日が開始日前となっても７日未満のはずなので、7で割った商は-1、これに+1で結果は０日とでる）
  以下は各曜日をサブクエリテーブルで与えて全曜日でチェックする場合。 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文法メモ１： || で文字列連結する。
  SQLite文法メモ２： strftimeの返値は文字列型なので、曜日番号も文字列として与えておかないと正しく比較できない。 サブクエリテーブルではカラム型が規定されないためらしい？
  SQLite文法メモ３： round(x): 小数点以下切り捨て , round(x,n): 第２引数で小数点第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:英文３文字略記名；jp1:日本語１文字表記)
  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' , '土' );
  
  --  本日の曜日、英文フルスペル、英文３文字略記名、日本語１文字表記
  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文法メモ１：こちらでは、weekname.id がintegerと確定しているので、自動変換してstrftimeの返値との比較は適切に行われる。

  取得例

  Dum.today	weekname.id	weekname.en_full	weekname.en3s	weekname.jp1
  2023-12-09	6	Saturday	Sat	土