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 : 日付処理 : 期間比較

日付や時刻で、開始と終了の期間で比較したいとき、4つの日付時刻値をどう比較するか迷うことが多いので、考え方を記録しておくことにしました。
以下は、SQLite 3.6 で検証しています

  • まずはcreate table

    SQLiteにおいては、カラム型名は、あんまり意味はないけど、一応、データの意図がわかりやすいように入れておきます。
    日付は、文字列'2013-04-12'か、数値 20130412 か、となるので、全ての日付用カラムの全ての行で、形式を統一しておかないとうまく比較できなくなります。 よって、check制約で書式統一を図ります。
    SQLiteの関数 date(), time(), datetime() などが返す書式に統一すると、日付時刻計算がやりやすいので、YYYY-mm-dd HH:MM:SS 書式とします。
    参考 SQLiteの日付時刻関数
    CREATE TABLE periodic (
      id INT ,
      title CHAR(50) NOT NULL,
      start_date DATE CHECK( start_date like '____-__-__'),
      end_date   DATE CHECK( end_date like '____-__-__')
    );
    CREATE INDEX  start_end on periodic  (start_date , end_date);
    
  • data 作成

    SQLiteでは、一行ずつinsert が必要。
    INSERT INTO periodic VALUES (1,'test 1','2010-10-10','2011-01-01');
    INSERT INTO periodic VALUES (2,'nest',  '2010-11-20','2010-12-01');
    INSERT INTO periodic VALUES (3,'over',  '2010-06-10','2012-12-31');
    INSERT INTO periodic VALUES (4,'pertial','2010-12-10','2012-03-01');
    INSERT INTO periodic VALUES (5,'pre',  '2009-12-31','2010-12-01');
    
  • 行抽出

    • 手始めに、ある日付を含む行抽出

      例 2010-11-30 を含む
      SELECT *
        FROM   periodic
        WHERE '2010-11-30' BETWEEN start_date AND end_date 
        ORDER BY start_date ;
      
      日付用カラムの書式が統一されていれば、文字列でも、数値でも、範囲比較が可能です。

    • ある期間を含む行を抽出

      期間を 2010-12-01 から 2011-01-31 として、この期間全体を含む行は
      SELECT *
        FROM   periodic
        WHERE  start_date <= '2010-12-01'  AND end_date >= '2011-01-31'
        ORDER BY start_date ;
      
      指定日を A から B として、以下に図示してみると 不等号方向が解りやすいでしょう。
       日付順序 | 先 →    →    → 後
       指定   |      A     B   
       カラム  | ↑start_date        ↑end_date
       比較式  |start_date <= A  and  B <= end_date 
      
    • 逆に指定期間内にある行を抽出

      期間を 2010-12-01 から 2011-01-31 として、この期間内に全期間が含まれる行は
      SELECT *
        FROM   periodic
        WHERE  start_date >= '2010-12-01'  AND end_date <= '2011-01-31'
        ORDER BY start_date ;
      
      指定日を A から B として、以下に図示してみると 不等号方向が解りやすいでしょう。
       日付順序 | 先 →    →    → 後
       指定   |  A             B   
       カラム  |    ↑start_date ↑end_date
       比較式  |  A <= start_date  and  end_date <= B
      
    • 指定期間の一部を含む

      期間を 2010-09-01 から 2011-01-31 として、この期間内に一部でも含まれる行は
      SELECT *
        FROM   periodic
        WHERE  start_date <= '2011-01-31'  AND end_date >= '2010-09-01'
        ORDER BY start_date ;
      
      指定日を A から B として、以下に図示してみると 不等号方向が解りやすいでしょう。
      日付順序| 先 →     →     → 後
       指定 |     A            B   
       行1 |      ↑start_date ↑end_date
       行2 | ↑start_date            ↑end_date
       行3 | ↑start_date      ↑end_date
       行4 |      ↑start_date       ↑end_date
       比較式|  start_date <= B    and    A <= end_date 
      
      4つの場合分けがあるので8つの比較式が必要と思ったでしょうが、実は、2つで済んでしまうのです。
      きもは、始まり同士の比較ではなく、一方の始まりともう一方の終わりを比較するところにあります。
  • 複数年に渡る期間に含まれる年度を抽出したい

    始まりと終了だけなら、それぞれ、extract などで計算できますが、間に挟まれる年度も一覧するには、年度の期間を保持しているtable も作って、テーブル結合を行うとよいでしょう
    • まずは、年度の期間を保持テーブル
      create table year_master (
        yeared int ,
        start_date DATE CHECK( start_date like '____-__-__'),
        end_date   DATE CHECK( end_date like '____-__-__')
      );
      create index year_period  on yeared (start_date , end_date) ;
      
      insert into  year_master values (2005,'2005-04-01','2006-03-31');
      insert into  year_master values (2006,'2006-04-01','2007-03-31');
      insert into  year_master values (2007,'2007-04-01','2008-03-31');
      insert into  year_master values (2008,'2008-04-01','2009-03-31');
      insert into  year_master values (2009,'2009-04-01','2010-03-31');
      insert into  year_master values (2010,'2010-04-01','2011-03-31');
      insert into  year_master values (2011,'2011-04-01','2012-03-31');
      insert into  year_master values (2012,'2012-04-01','2013-03-31');
      insert into  year_master values (2013,'2013-04-01','2014-03-31');
      insert into  year_master values (2014,'2014-04-01','2015-03-31');
      insert into  year_master values (2015,'2015-04-01','2016-03-31');
      
      一応10年分のデータを用意しましたがマスターテーブルとして、保持しておくだけなので、必要期間のみでもよいでしょう。 もし、大学など秋始まりだったり、旧暦での年度にしたいときは、start_date, end_date の日付をずらして保存します。
    • さて、年度一覧
      select b.yeared as yeared, b.start_date as year_start, b.end_date as year_end,  a.*
       from year_master as b inner join  periodic as a 
         on  a.start_date <= b.end_date  and  a.end_date >= b.start_date 
       order by a.id, b.yeared  ;
      
      これを図解してみます。 単年度を 開始 s1 , 終了 e1 のようにすると、次の年度データは翌日から s2 で始まって e2終了となります
      日付順序| 先 →     →     → 後
       年度 | s1  e1, s2  e2, s3  e3, s4  e4, s5  e5, s6  e6
       行1 |  start_date↑ ↑end_date
       行2 |  ↑start_date ↑end_date
       行3 | ↑start_date      ↑end_date
       行4 |            ↑start_date     ↑end_date
       比較式|  a.start_date <= b.e    and    b.s <= a.end_date 
      
      取得出来るデータは、
      行1について、 s2 で始まる1行、
      行2は、s1およびs2で始まる2行、
      行3は、s1, s2, s3で始まる3行、
      行4は、s3, s4, s5で始まる3行、
      となります。
      
  • タイムカード

    • データ作成

      タイムカードでは、時刻カラムが一つで、inとoutを区別するカラムを作ることが多いでしょう。 inとoutを区別するカラムは、check制約で入力できるデータを制限する方法をとることにします。
      create table timecard (
        id  integer primary key autoincrement,
        code char(10) not null,	/* 社員コードなど */
        record_time datetime check( record_time like '____-__-__ __:__:__'),
        in_out   not null  check( in_out='in'  or  in_out='out' ),
      );
      create index  code_time  on timecard (code , record_time);
      
      --  各時刻に1行づつ記録
      insert into timecard (code,record_time, in_out) values ('a', '2013-04-12 09:00:00', 'in');
      insert into timecard (code,record_time, in_out) values ('a', '2013-04-12 17:00:00', 'out');
      
    • timecard の in から out までの経過時間を算出したい

      一人の社員(codeカラムの1データ)において、時刻順に並べると、in と out が交互に出てくることが大前提ではありますが、その整合性チェックは済んでいるものとして時刻の比較をします。
      in と outの時刻が同じカラムで別の行となるので、in 用とout用に自己結合を行います。 in用の時刻が先で、out用の時刻が後になる行を連結、さらに、out用を直後の1件にしたいので、group化を行って、最小値をとります。
      時刻の差を求めるには、1970-01-01からの経過秒数(unixepoch) を求めて引き算します。 詳細は、strftime()を参照してください。
      strftime format 書式例
      datetime(...)  のフォーマット     : strftime("%Y-%m-%d %H:%M:%S", ...)
      1970-01-01からの経過秒数(unixepoch)  : strftime("%s", ...)
      曜日番号(日曜 0 開始)         : strftime("%w", ...)
      年間週数(元旦 00 開始 大晦日53週目)  : strftime("%W", ...)
      年間日数(元旦 001、大晦日365,閏年366) : strftime("%j", ...)
      
      select in_data.code, in_data.record_time as in_time, min( out_data.record_time ) as out_time
         , time(  strftime('%s', min( out_data.record_time ),'localtime') - strftime('%s', in_data.record_time,'localtime') , 'unixepoch' ) as inout_interval
       from  timecard as in_data  inner join  timecard as out_data
         on   in_data.code=out_data.code and in_data.record_time < out_data.record_time 
           and  in_data.in_out = 'in' and out_data.in_out = 'out'
       group by in_data.code, in_data.record_time ;
      
      SQLiteでは、select句の前方で指定したエイリアス名をその直ぐ後方でつかえないので、何度も同じ計算式が必要になってしまうのが欠点ではあります。 そこで、この計算がよく使われるなら、 code, in_time, out_time の3つを求めるクエリをviewにしておくと便利でしょう。
      create view inoutcard as select in_data.code as code, in_data.record_time as in_time, min( out_data.record_time ) as out_time
       from  timecard as in_data  inner join  timecard as out_data
         on   in_data.code=out_data.code and in_data.record_time < out_data.record_time 
           and  in_data.in_out = 'in' and out_data.in_out = 'out'
       group by in_data.code, in_data.record_time ;
      
      -- このview を使って、in_time から out_time の時間間隔計算
      select code ,in_time , out_time 
         , time(  strftime('%s', out_time,'localtime') - strftime('%s', in_time,'localtime') , 'unixepoch' ) as inout_interval
        from inoutcard 
        order by code,in_time ;
      
      view の自己結合をサブクエリにする場合は以下。
      create view inoutcard as 
       select in_data.code as code, in_data.record_time as in_time
        , ( select min(t1.record_time)  from  timecard as  t1
              where t1.in_out = 'out' and t1.code=in_data.code  and  in_data.record_time < t1.record_time
           ) as out_time
       from   timecard as in_data 
       where in_out = 'in' ;
      
    日付時刻の関数は、データベースによって実装が異なるので、 MySQLでの日付期間比較 もしてみました。
  • 区間データ内の比較

    • 区間データの各行どうしの重なりの調査(ダブルブッキング調査)

      予定表などでの予定時間重複有無調査を考える場合、mysqlでは、DATETIME 型としました。 SQLiteも同様の書式に合わせることで、同じ事が出来ます。
      しかし、せっかくですから、少し変更して、日付をまたぐことのないデータとして、日付カラム一つと開始時刻および終了時刻を保持するようにしてみます。
      CREATE TABLE periodic_dt (
        id int ,
        title varchar(50) NOT NULL,
        target_d DATE  CHECK( target_d like '____-__-__'),
        start_t  TIME  CHECK( start_t like '__:__:__'),
        end_t    TIME  CHECK(   end_t like '__:__:__' and  start_t < end_t )
      );
      CREATE UNIQUE INDEX  start_end on periodic_dt  (target_d , start_t , end_t);
      
      --  UNIQUE INDEX で同じ日において、開始と終了とのどちらも同じとなる行が存在することはないようにします。
      --  また、部屋ごとの時間割などにするときは、その識別カラムもUNIQUE INDEXに含めること
      
      同じテーブル内の期間で比較するので、自己結合を使います。 DATETIME 型を使ったときと違うのは、日付カラムが別になっているので、これの比較条件も常に必要と言うことです。
      select  pre_dt.target_d target , pre_dt.start_t pre_start , pre_dt.end_t pre_end
          , count(next_dt.start_t) overlap_count
          , group_concat(' | ' , concat(next_dt.start_t,'~',next_dt.end_t)) overlap_period
        from  periodic_dt pre_dt  left join  periodic_dt next_dt
            on    pre_dt.start_t <= next_dt.end_t  and  next_dt.start_t <= pre_dt.end_t
              and ( pre_dt.start_t <> next_dt.start_t or pre_dt.end_t <> next_dt.end_t )  --  自己結合なので、自分は除外
              and pre_dt.target_d = next_dt.target_d  --  同じ日付についてのみチェック
        group by  pre_dt.target_d , pre_dt.start_t , pre_dt.end_t  --  unique を識別するカラム列挙
      ;
      
      結合条件式は「区間の一部を含む場合」を参照して下さい。
      これを図解してみます。
      「元データ例」
      日付順序| 先 →     →     → 後
       行1 |  start_t.1↑    ↑end_t.1
       行2 | ↑start_t.2  ↑end_t.2
       行3 |  ↑start_t.3      ↑end_t.3
       行4 |    start_t.4↑ ↑end_t.4
       行5 |         ↑start_t.5   ↑end_t.5
       行6 |↑start_t.6↑end_t.6
       行7 |         start_t.7↑  ↑end_t.7
      
      結合比較式| pre_dt.start_t <= next_dt.end_t  and  next_dt.start_t <= pre_dt.end_t
      「pre_dt の行1に対する抽出列」
      行1ー行2| ↑start_t.2 ↑start_t.1 ↑end_t.2 ↑end_t.1
      行1ー行3| ↑start_t.3 ↑start_t.1 ↑end_t.1 ↑end_t.3
      行1ー行4| ↑start_t.1 ↑start_t.4 ↑end_t.4 ↑end_t.1
      行1ー行5| ↑start_t.1 ↑start_t.5 ↑end_t.1 ↑end_t.5
      「group 化 後 各カラム値」
      pre_start     |  start_t.1 
      pre_end       |  end_t.1 
      overlap_count |  4 
      overlap_period| "start_t.2~end_t.2 | start_t.3~end_t.3 | start_t.4~end_t.4 | start_t.5~end_t.5"
      
      上記は、連続する場合「 pre_dt.end_t = next_dt.start_t 」を含んでいますが、 これを隙間はないが重複とはみなしたくない時は、on 句条件の等号を削って以下とします。
      select  pre_dt.target_d target_date , pre_dt.start_t pre_start , pre_dt.end_t pre_end
          , count(next_dt.start_t) overlap_count
          , group_concat(' | ' , concat(next_dt.start_t,'~',next_dt.end_t)) overlap_period
          , group_concat(' , ' , next_dt.id) double_booking
          , pre_dt.id target_id
        from  periodic_dt pre_dt  left join  periodic_dt next_dt
            on    pre_dt.start_t < next_dt.end_t  and  next_dt.start_t < pre_dt.end_t
              and ( pre_dt.start_t <> next_dt.start_t or pre_dt.end_t <> next_dt.end_t )
              and pre_dt.target_d = next_dt.target_d 
        group by  pre_dt.target_d , pre_dt.start_t , pre_dt.end_t , pre_dt.id  ;
      
      これで、overlap_count が 0 ではない行はダブルブッキング発生!です。
    • 区間データの隙間(空き時間)を取り出す

      上記の連続を含む場合で、overlap_count が 0 のものは、他と重なっても連続してもいません。 しかし、この場合は、両側に隙間があるのもだけとなり、どちらか一方に隙間が有る場合がはじかれてしまいます。
      そこで、後方に隙間が有る場合(隙間の前方時刻)のみを抽出して、それより後方で、最初の開始区間をとると空き時間が得られます。
      select  target , pre_end free_start
        , (select  min(post_free.start_t)  from  periodic_dt post_free 
             where  post_free.start_t > nowrap.pre_end
          ) free_end
        from ( select  pre_dt.target_d target , pre_dt.end_t pre_end
             from  periodic_dt pre_dt  left join  periodic_dt next_dt
                 on    pre_dt.end_t >= next_dt.start_t  and  pre_dt.end_t < next_dt.end_t
                   and pre_dt.target_d = next_dt.target_d  --  同じ日付についてのみチェック
             where  next_dt.start_t is null
            ) nowrap 
        order by  target , free_start ;
      
      これを図解してみます。
      「元データ例」
      日付順序 | 先 →    →     → 後
       行1n |   start_t.1n↑    ↑end_t.1n
       行2n | ↑start_t.2n ↑end_t.2n
       行3n |           start_t.3n↑   ↑end_t.3n
       行4n |↑start_t.4n↑end_t.4n
      
      from句のsubquery nowrap では、on 句条件は、終了と開始が連続するものも含めておき、 where 句で next_t側がnullとなる条件で、重複や連続を除外します。
      これにより 行1n,行3n が抽出されるので、各区間後方の空き時間は、下記 ** の区間となる
      「抽出行」
       行1n |   start_t.1n↑ ↑end_t.1n ** (free_end = start_t.3n)
       行3n |           start_t.3n↑   ↑end_t.3n ** (free_end is null)
      

[設置日 2013-04-13] : 初回設置
[追加 2013-09-28] : 区間データ内の比較(重複=ダブルブッキング調査、空き時間調査) を追加
[編集 2014-12-07] : html5 の 新要素をとりいれてマークアップし直してみる
[編集 2016-11-03] : strftime()の書式例を追加
[最終更新日 2018-09-04]

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