SQLiteでの曜日計算

side menu

  • めも一覧

  • GD 関連

    • GDによる画像読み書き簡易化関数の作成
    • GD 利用、bitmap 形式画像の読み書き
    • カウントダウン日数画像出力ソース配布と設置解説
  • SQL関連一覧

    • MySQLでの日付期間比較
    • SQLiteでの日付期間比較
    • SQLiteでの連続日付view作成=カレンダーテーブル
    • SQLiteでの曜日計算
    • MySQLでヒストグラム作成
    • 入れ子集合モデルでサイトマップ by SQLite
    • 入れ子集合モデルでサイトマップ by MySQL
    • ランク付け:by MySQL
    • 抜けデータ補完:by MySQL
    • カンマ区切りデータ:by MySQL
    • row_number関数を利用して当番表作成:by MySQL8,mariaDB10
    • window関数解説:by MySQL8,mariaDB10
  • その他

    • phpによる月齢計算クラス
  • miztools のサイトマップ
     このサイトのrss
    miztools の更新履歴

SQLiteでの曜日計算

SQLiteでの曜日計算は、strftime(format, timestring, modifier, modifier, ...)関数で、format に %w を指定することで、日曜0開始の数値を得ることで行う。
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
    2025-05-15 4 木
  • 日付カラムから曜日算出

    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
    2025-05-15 4 Thursday Thu 木

[設置日 2017-06-03] : 初回設置
[最終更新日 2018-09-04]

| ページtopへ | miztools top | site map | cake.org