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では、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-09-28] : 区間データ内の比較(重複=ダブルブッキング調査、空き時間調査) を追加
[編集 ] : html5 の 新要素をとりいれてマークアップし直してみる
[編集 ] : strftime()の書式例を追加
[最終更新日 ]