SQLite _ 日付期間比較

SQLite : 日付処理　：　期間比較

日付や時刻で、開始と終了の期間で比較したいとき、４つの日付時刻値をどう比較するか迷うことが多いので、考え方を記録しておくことにしました。
以下は、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 　　
　行１　｜　　　　　　↑start_date　↑end_date
　行２　｜　↑start_date　　　　　　　　　　　　↑end_date
　行３　｜　↑start_date　　　　　　↑end_date
　行４　｜　　　　　　↑start_date　　　　　　　↑end_date
　比較式｜  start_date <= B    and    A <= end_date

４つの場合分けがあるので８つの比較式が必要と思ったでしょうが、実は、２つで済んでしまうのです。
きもは、始まり同士の比較ではなく、一方の始まりともう一方の終わりを比較するところにあります。

複数年に渡る期間に含まれる年度を抽出したい

始まりと終了だけなら、それぞれ、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
　行１　｜　　start_date↑　↑end_date
　行２　｜　　↑start_date　↑end_date
　行３　｜　↑start_date　　　　　　↑end_date
　行４　｜　　　　　　　　　　　　↑start_date　　　　　↑end_date
　比較式｜  a.start_date <= b.e    and    b.s <= a.end_date 

取得出来るデータは、
行１について、 s2 で始まる１行、
行２は、s1およびs2で始まる２行、
行３は、s1, s2, s3で始まる３行、
行４は、s3, s4, s5で始まる３行、
となります。

タイムカード

データ作成

タイムカードでは、時刻カラムが一つで、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);

--  各時刻に１行づつ記録
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カラムの１データ)において、時刻順に並べると、in と out が交互に出てくることが大前提ではありますが、その整合性チェックは済んでいるものとして時刻の比較をします。
in と outの時刻が同じカラムで別の行となるので、in 用とout用に自己結合を行います。 in用の時刻が先で、out用の時刻が後になる行を連結、さらに、out用を直後の１件にしたいので、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 の３つを求めるクエリを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 を識別するカラム列挙
;

結合条件式は「区間の一部を含む場合」を参照して下さい。
これを図解してみます。

「元データ例」
日付順序｜　先　→　　　　　→　　　　　→　後
　行１　｜　　start_t.1↑　　　　↑end_t.1
　行２　｜　↑start_t.2　　↑end_t.2
　行３　｜　　↑start_t.3　　　　　　↑end_t.3
　行４　｜　　　　start_t.4↑　↑end_t.4
　行５　｜　　　　　　　　　↑start_t.5　　　↑end_t.5
　行６　｜↑start_t.6↑end_t.6
　行７　｜　　　　　　　　　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 の行１に対する抽出列」
行１ー行２｜　↑start_t.2 ↑start_t.1 ↑end_t.2 ↑end_t.1
行１ー行３｜　↑start_t.3 ↑start_t.1 ↑end_t.1 ↑end_t.3
行１ー行４｜　↑start_t.1 ↑start_t.4 ↑end_t.4 ↑end_t.1
行１ー行５｜　↑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 ;

これを図解してみます。

「元データ例」
日付順序　｜　先　→　　　　→　　　　　→　後
　行１ｎ　｜　　　start_t.1n↑　　　　↑end_t.1n
　行２ｎ　｜　↑start_t.2n　↑end_t.2n
　行３ｎ　｜　　　　　　　　　　　start_t.3n↑　　　↑end_t.3n
　行４ｎ　｜↑start_t.4n↑end_t.4n

from句のsubquery nowrap では、on 句条件は、終了と開始が連続するものも含めておき、 where 句で next_t側がnullとなる条件で、重複や連続を除外します。
これにより　行１ｎ，行３ｎ　が抽出されるので、各区間後方の空き時間は、下記 ** の区間となる

「抽出行」
　行１ｎ　｜　　　start_t.1n↑　↑end_t.1n ** (free_end = start_t.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]

SQLite : 日付処理 ： 期間比較

まずはcreate table

data 作成

行抽出

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

ある期間を含む行を抽出

逆に指定期間内にある行を抽出

指定期間の一部を含む

複数年に渡る期間に含まれる年度を抽出したい

タイムカード

データ作成

timecard の in から out までの経過時間を算出したい

区間データ内の比較

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

区間データの隙間（空き時間）を取り出す

SQLite : 日付処理　：　期間比較