MySQL _ 日付期間比較

MySQL : 日付処理　：　期間比較

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

まずはcreate table

CREATE TABLE `periodic` (
  `id` INT ,
  `title` VARCHAR(50) NOT NULL,
  `start_date` DATE,
  `end_date`   DATE,
 KEY `start_end` (`start_date` , `end_date`)
);

data 作成

INSERT INTO `periodic` VALUES
 (1,'test 1','2010-10-10','2011-01-01'), (2,'nest','2010-11-20','2010-12-01'),
 (3,'over',  '2010-06-10','2012-12-31'), (4,'pertial','2010-12-10','2012-03-01'),
 (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 ;
```
左辺にカラム名ではなく固定文字列を入れる場合、日付や時刻で計算させたいときは、 cast しておくのが無難です。特に、右辺の型がdatetime だったり左辺と形式の異なる場合に重要です。
参考　 MySQLの日付比較：比較対象のカラムは型を意識しなければならない。
datetime 型でstart_date,end_date を保持していた場合は、以下のように左辺を cast
```
SELECT *
  FROM `periodic`
  WHERE  CAST('2010-11-30' AS DATE) 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 , end_date date
, key  (start_date , end_date)
);

insert into  `year_master` values  (2000,'2000-04-01','2001-03-31')
 ,(2001,'2001-04-01','2002-03-31'),(2002,'2002-04-01','2003-03-31'),(2003,'2003-04-01','2004-03-31'),(2004,'2004-04-01','2005-03-31')
 ,(2005,'2005-04-01','2006-03-31'),(2006,'2006-04-01','2007-03-31'),(2007,'2007-04-01','2008-03-31'),(2008,'2008-04-01','2009-03-31')
 ,(2009,'2009-04-01','2010-03-31'),(2010,'2010-04-01','2011-03-31'),(2011,'2011-04-01','2012-03-31'),(2012,'2012-04-01','2013-03-31')
 ,(2013,'2013-04-01','2014-03-31'),(2014,'2014-04-01','2015-03-31'),(2015,'2015-04-01','2016-03-31'),(2016,'2016-04-01','2017-03-31')
 ,(2017,'2017-04-01','2018-03-31'),(2018,'2018-04-01','2019-03-31'),(2019,'2019-04-01','2020-03-31'),(2020,'2020-04-01','2021-03-31') ;

一応20年分のデータを用意しましたがマスターテーブルとして、保持しておくだけなので、必要期間のみでもよいでしょう。もし、大学など秋始まりだったり、旧暦での年度にしたいときは、start_date, end_date の日付をずらして保存します。

さて、年度一覧

select *
 from `year_master` as b inner join `periodic` 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を区別するカラムは、bool や byte 型でもよいのですが、以下では、enum型で入力できるデータを制限する方法をとります。

create table `timecard` (
  `id` int primary key  auto_increment,
  `code` varchar(10) not null,	/* 社員コードなど */
  `record_time` datetime,
  `in_out`  enum ('in','out')  not null,
 key `c` (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');
--  以下、一斉　就業、退社
set @t = cast( now() as datetime), @io = 'in' ;
insert into `timecard` (code,record_time, in_out) values ('a', @t, @io) , ('b', @t, @io), ('c', @t, @io);
set @t = cast( now() as datetime), @io = 'out' ;
insert into `timecard` (code,record_time, in_out) values ('a', @t, @io) , ('b', @t, @io), ('c', @t, @io);

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

一人の社員(codeカラムの１データ)において、時刻順に並べると、in と out が交互に出てくることが大前提ではありますが、その整合性チェックは済んでいるものとして時刻の比較をします。
in と outの時刻が同じカラムで別の行となるので、in 用とout用に自己結合を行います。 in用の時刻が先で、out用の時刻が後になる行を連結、さらに、out用を直後の１件にしたいので、group化を行って、最小値をとります。

select in_data.code, in_data.record_time as in_time, min( out_data.record_time ) as out_time
   , TIMEDIFF( min( out_data.record_time ) , in_data.record_time ) 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 ;

MySQLはサブクエリの実行が遅いので、件数が増えると、join後のgroup化でindexが使えないのとどっちが遅いか早いかわかりませんが、サブクエリを使った場合は以下。

select *, TIMEDIFF( out_time , in_time ) as inout_interval
 from ( 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'
        order by code, in_time 
      ) as t2 ;

日付時刻の関数は、データベースによって実装が異なるので、 SQLiteでの日付期間比較もしてみました。

区間データ内の比較

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

予定表などでの予定時間重複有無調査を考える場合、 DATETIME 型で開始終了時刻を保持するようにします。日付をまたぐことのないデータの場合については、SQLite版で考察しています。

CREATE TABLE `periodic_dt` (
  `id` int ,
  `title` varchar(50) NOT NULL,
  `start_dt` DATETIME,
  `end_dt`   DATETIME,
unique KEY `start_end` (`start_dt` , `end_dt`) ,
check ( `start_dt` < `end_dt` )
);
--  unique KEY で開始と終了とのどちらも同じとなる行が存在することはないようにします。
--  また、部屋ごとの時間割などにするときは、その識別カラムもunique keyに含めること

同じテーブル内の期間で比較するので、自己結合を使います。

select  pre_dt.start_dt pre_start , pre_dt.end_dt pre_end
    , count(next_dt.start_dt) overlap_count
    , group_concat(' | ' , concat(next_dt.start_dt,'~',next_dt.end_dt)) overlap_period
  from  periodic_dt pre_dt  left join  periodic_dt next_dt
      on    pre_dt.start_dt <= next_dt.end_dt  and  next_dt.start_dt <= pre_dt.end_dt
        and ( pre_dt.start_dt <> next_dt.start_dt or pre_dt.end_dt <> next_dt.end_dt )  --  自己結合なので、自分は除外
  group by  pre_dt.start_dt , pre_dt.end_dt    --  unique を識別するカラム列挙
;

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

「元データ例」
日付順序｜　先　→　　　　　→　　　　　→　後
　行１　｜　　　start_dt.1↑　　　　↑end_dt.1
　行２　｜　↑start_dt.2　↑end_dt.2
　行３　｜　　↑start_dt.3　　　　　　↑end_dt.3
　行４　｜　　　　start_dt.4↑　↑end_dt.4
　行５　｜　　　　　　　　　↑start_dt.5　　　↑end_dt.5
　行６　｜↑start_dt.6↑end_dt.6
　行７　｜　　　　　　　　　　start_dt.7↑　　　↑end_dt.7

結合比較式｜　pre_dt.start_dt <= next_dt.end_dt  and  next_dt.start_dt <= pre_dt.end_dt
「pre_dt の行１に対する抽出列」
行１ー行２｜　↑start_dt.2 ↑start_dt.1 ↑end_dt.2 ↑end_dt.1
行１ー行３｜　↑start_dt.3 ↑start_dt.1 ↑end_dt.1 ↑end_dt.3
行１ー行４｜　↑start_dt.1 ↑start_dt.4 ↑end_dt.4 ↑end_dt.1
行１ー行５｜　↑start_dt.1 ↑start_dt.5 ↑end_dt.1 ↑end_dt.5
「group 化　後　各カラム値」
pre_start     |  start_dt.1 
pre_end       |  end_dt.1 
overlap_count |  4 
overlap_period| "start_dt.2~end_dt.2 | start_dt.3~end_dt.3 | start_dt.4~end_dt.4 | start_dt.5~end_dt.5"

上記は、連続する場合「 pre_dt.end_dt = next_dt.start_dt 」を含んでいますが、これを隙間はないが重複とはみなしたくない時は、on 句条件の等号を削って以下とします。

select  pre_dt.start_dt pre_start , pre_dt.end_dt pre_end
    , count(next_dt.start_dt) overlap_count
    , group_concat(' | ' , concat(next_dt.start_dt,'~',next_dt.end_dt)) 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_dt < next_dt.end_dt  and  next_dt.start_dt < pre_dt.end_dt
        and ( pre_dt.start_dt <> next_dt.start_dt or pre_dt.end_dt <> next_dt.end_dt ) 
  group by pre_dt.start_dt , pre_dt.end_dt ;

これで、overlap_count が 0 ではない行はダブルブッキング発生！です。

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

上記の連続を含む場合で、overlap_count が 0 のものは、他と重なっても連続してもいません。しかし、この場合は、両側に隙間があるのもだけとなり、どちらか一方に隙間が有る場合がはじかれてしまいます。
そこで、後方に隙間が有る場合（隙間の前方時刻）のみを抽出して、それより後方で、最初の開始区間をとると空き時間が得られます。

select  pre_end free_start
  , (select  min(post_free.start_dt)  from  periodic_dt post_free 
       where  post_free.start_dt > nowrap.pre_end
    ) free_end
  from ( select  pre_dt.end_dt pre_end
       from  periodic_dt pre_dt  left join  periodic_dt next_dt
           on  pre_dt.end_dt >= next_dt.start_dt  and  pre_dt.end_dt < next_dt.end_dt
       where  next_dt.start_dt is null
      ) nowrap 
  order by free_start ;

これを図解してみます。

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

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

「抽出行」
　行１ｎ　｜　　　start_dt.1n↑　↑end_dt.1n ** (free_end = start_dt.3n)
　行３ｎ　｜　　　　　　　　　　　start_dt.3n↑　　　↑end_dt.3n ** (free_end is null)

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

MySQL : 日付処理 ： 期間比較

まずはcreate table

data 作成

行抽出

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

ある期間を含む行を抽出

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

指定期間の一部を含む

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

タイムカード

データ作成

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

区間データ内の比較

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

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

MySQL : 日付処理　：　期間比較