mySQLとfuelPHP time型の合計計算とdatetime型の合計計算
web勤怠管理のシステムが一段落しました（多分）
今回は使用しているmySQLで苦戦したこと。
勤務時間の計算元になる出勤時間や退勤時間等がデータベースにtime型を使っていたのですが、datetime型の方が計算しやすいと先輩は仰います。
時間の計算するのに日付部分なんていらないと思い、time型にしてしまったのですが、いざ計算しようとするとこれが結構大変でした。
今回はtime型とdatetime型の計算の仕方について調べてみました。
さて、time型での勤務時間の計算です。
time型同士の時間差を計算するのにTIMEDIFFと言う関数があります。
使い方は
TIMEDIFF( time2 , time1 );
第1引数から第２引数を引いた時間を計算してくれます。
time2が18:00:00でtime1が10:00:00だったら08:00:00が返ってきます。
2と1を逆にしたのは出勤が１で退勤が２のイメージだったからです・・・
これにさらに休憩時間(time3)の差を計算します。
TIMEDIFF( TIMEDIFF( time2 , time1 ) , time3 );
一度に書くとこんな感じ。time3が01:00:00なら07:00:00が戻ります。
勘の良い方は気づいてるかもしれませんが、time型というのはわざわざ関数を使用しないと計算出来ない型だったりします。
time2 - time1 みたいな書き方をしてもExcelのように計算してくれないちょっと融通の利かない型なのです。
Excelの場合は見た目だけそうなっているだけで中身はちゃんとした数値が入ってますが・・・
1日分だけだったらまだいいかもしれませんが、これが1ヶ月分の合計勤務時間を出す、という文章になると少し面倒になります。
プログラムを絡ませれば変数一つ用意して２０１３年11月のみの結果を1個1個forとかでループして加算するだけでいいのですが、まずはSQL環境のみとします。
時間とは別にDATE型で日付のカラムがあるものとし、２０１３年11月の勤務時間の合計を出す文になるとtime型だけの計算ではちょっとどうしようもないです。
合計を計算する関数にSUMがありますがこれはもちろんtime型を足し算してくれません。
どうにかしてtime型に入っている数値を数値化しなければなりませんね。
time型を数値に直す手段としてTIME_TO_SECという関数がありました。名前の通り、time型を秒に変換する関数です。
例えば08:00:00というtime型があったら60*60*8で28800(秒)が返ってきます。
この28800は逆にSEC_TO_TIMEを使ってtime型の08:00:00に変換し直すことも出来ます。
これらを踏まえてそれっぽいSQL文を書くと・・・
select SEC_TO_TIME(
SUM(
TIME_TO_SEC( TIMEDIFF( TIMEDIFF( time2 , time1 ) , time3 ) )
)
) as '月勤務時間'
from 勤怠テーブル where 日付 like '2013-11-%';
こんな感じ。()の数が多くてややこしいですね。
select * from 勤怠テーブル where 日付 like '2013-11-%'
のみのSQL文を使います。これならQueryBuilderでも簡単に書けますね。
$query = DB::select()->from('勤怠テーブル')->where('日付' , 'like' , '2013-11-%')
->execute()->as_array();
for($i = 0 ; $i < count($query) ; $i++)
{
$hour = $hour + substr( $query[$i]['time2'] , 0 , 2 ) -
substr( $query[$i]['time1'] , 0 , 2 ) -
substr( $query[$i]['time3'] , 0 , 2 );
$minute = $minute + substr( $query[$i]['time2'] , 3 , 2 ) -
substr( $query[$i]['time1'] , 3 , 2 ) -
substr( $query[$i]['time3'] , 3 , 2 );
}
$hour = $hour + floor( $minute / 60 );
$minute = $minute % 60;
echo $hour.':'.$minute.':00';
データの秒部分が00のみだとするとこんな感じ。
phpの時間系の関数を使わず、time型が必ず'hh:mm:ss'になってるのをいいことにsubstrを使ったちょっと強引なプログラムです。
PHPが文字列の数字も数値と見て計算してくれるから出来る書き方ですね。
スマートな計算方法として使えそうなのがPHPのDateTimeクラスを用いた計算。
メソッドチェインを使って計算していくみたいです。
ただ、計算しやすい代わりに書き方に癖がありました。
$datet = new DateTime('2013-11-26 10:00:00');
$datet2 = new DateTime('2013-11-26 19:00:00');
echo $datet2->diff($datet)->format('%H:%I:%S');
この様に書くとechoで表示される文が 09:00:00 になります。
見れば内容は何となく分かりますが表示のさせ方にちょっと癖がありますね。
format('%Y-%M-%D %H:%I:%S');
と書くと 00-00-00 09:00:00 と表示されます。$datet2の年部分を3013にすると 1000-00-00 09:00:00 になります。
また、アルファベットを小文字にすると余分な０が表示されなくなり、 0-0-0 9:0:0 と表示されます。
('2013-11-26 10:00:00') の部分はmySQLのdatetime型 がそのまま入れられるので、先ほどのtime1やtime2がdatetime型だった場合、
$query = DB::select()->from('勤怠テーブル')->where('日付' , 'like' , '2013-11-26')
->execute()->as_array();
$datet = new DateTime($query[0]['time1']);
$datet2 = new DateTime($query[0]['time2']);
echo $datet2->diff($datet)->format('%H:%I:%S');
こんな感じで変数を入れても動きます。
と言う感じで２つの"時刻"の引き算はごく簡単に出来るのですが、"時間"の足し引き算はさらに癖が強い関数を使います。
使用する関数は加算の場合はadd()、減算の場合はsub()を使います。
例えば$datetに1時間追加した値を出したいときは
$datet = new DateTime('2013-11-26 10:00:00');
$datet = $datet->add(new DateInterval('PT1H') );
echo $datet->format('Y-m-d H:i:s');
これで 2013-11-26 11:00:00 が表示されますがPT1Hってなんぞや、て感じですね。
見た目は分かりにくいですが書き方の法則性は分かりやすいです。
DateInterval()に書くのは
'P年Y月M日DT時H分M秒S'
漢字の部分を全て半角数字に置き換えて書きます。
例えば1年２月3日 4時間5分6秒追加したいなら 'P1Y2M3DT4H5M6S' と書きます。
P～Tの手前までが年月日、T以降が時間を書く場所と見れば分かりやすいかと思います。
不必要な部分は省略が可能ですが、先頭のPだけは必須です。
なので時間だけ加減算したいならPT1Hとかになるわけです。
Pは期間を意味するPeriod、TはTimeのことだと思います。
ちなみにPT01Hでも1時間で計算してくれるので、データベースから持ってきたdatetimeの一部分だけの文字列をそのまま入れても大丈夫っぽいです。
注意点としてこのadd()やsub()はdiff()をした後には使えなくなると言う点があります。
理由はadd()とsub()はDateTime型を返り値としていますが、diff()はDateInterval型を返り値としているからです。
また、DateTime型のformat()とDateInterval型のformat()の仕様が違うため、%の有無や、大文字小文字の違いがあったりします。
なので勤務時間を出すときは 退勤時間 - 出勤時間 - 休憩時間 ではなく、
退勤時間 - (出勤時間 + 休憩時間 ) と考えると分かりやすいです。
ですがformat()後に表示される文字列はDateTimeの文字列になるので無理やりnew DateTimeで作り直すということもできます。
$datet = new DateTime('2013-11-26 10:00:00');
$datet2 = new DateTime('2013-11-26 19:00:00');
$datet2 = new DateTime( $datet2->diff($datet)->format( '%H:%I:%S' ) );
$datet2 = $datet2->sub(new DateInterval('PT1H'));
echo $datet2->format('H:i:s');
時間関係のデータを扱う場合は”時刻”と”時間”が別のものであると意識すると計算する時に迷わなくて済みそうです。
