時刻の計算1
◆時刻の差の計算
2つの日付の間の時間を知りたい→Date を使う。
新たに Date_Span オブジェクトを作成してsetFromDateDiffメソッド
を使用し、toDaysを用いて正確な日数を取得します。
例 40-3時刻の差の計算
<?php require_once 'Date.php'; $someDate = new Date('1813-02-23T05:34:23'); $otherDate = new Date('1789-12-21T18:23:42'); $span = new Date_Span(); $span->setFromDateDiff($someDate, $otherDate); // 時刻の差を日付単位で表示します: 8463,46575231 echo $span->toDays(); // 時刻の差を年数で表示します: echo (int)($span->toDays() / 365); ?>
エラー処理の極意
仕事をしていると細かいエラーに悩まされることが多々あります。
そんなときどういった対処をするか?
これを用意しておくことで、
エラーの原因解決に行き着く時間短縮することができます。
エラー処理はこちら↓
select apply_end_time - apply_start_time - rest_time - over_rest_time from trn_work_time where id = 40; ←エラー発生 どこでエラーが出てるかわかりません。
そんな時は徐々に問題解決をしていく。
①select rest_time from trn_work_time where id=40; →問題なし ②select over_rest_time from trn_work_time where id=40; →問題なし ③select apply_end_time - apply_start_time from trn_work_time where id=40; →問題なし ④select apply_end_time - apply_start_time - rest_time from trn_work_time where id=40; →エラー発生 原因はrest_timeがnullのときがあったから... ⑤select apply_end_time - apply_start_time - rest_time - over_rest_time from trn_work_time where id=40; 問題解決
時間関数
<?= strtotime('2009-01-29 17:30:00') ?> 1233217800 <?= strtotime('2009-01-29 17:30:00') - strtotime('2009-01-29 16:45:00') ?> 2700 <?= date('H:i', 2700) ?> 09:45 <?= gmdate('H:i', 2700) ?> 00:45 <?= strtotime('0:45') ?> //一定時刻から現時国までの秒数 1233157500 <?= date('H:i' , 1233157500) ?>//秒数を時間と分に変換 00:45 <?= date('Y-m-d H:i:s',1233157500) ?> //秒数を年月日時間分秒に変換 2009-01-29 00:45:00 <? $i = strtotime('01:00') + strtotime('01:00'); echo strtotime('%X', $i); ?> <? $i = strtotime('01:00') + strtotime('01:00'); echo $i ; ?> 2466316800 <?= strtotime('01:00'); 1233158400 <?$i = 1233158400 % 86400; echo $i; 57600 <?= strtotime('01:00') - strtotime('00:00');
SQL
◆ID10が当てはまるものすべて出力
SELECT COALESCE ((apply_end_time - apply_start_time)::time,'00:00'::time) - rest_time - COALESCE(over_rest_time,'00:00'::time) as work_time FROM trn_work_time WHERE user_id=10;
★サブクエリにして合計を算出出力
SELECT sum(work_time) from (SELECT COALESCE((apply_end_time - apply_start_time)::interval,'00:00'::interval) - COALESCE(rest_time::interval,'00:00'::interval) - COALESCE(over_rest_time::interval,'00:00'::interval) as work_time FROM trn_work_time WHERE user_id=10) AS work_time;
★group byで合計を求める
select SUM(COALESCE((apply_end_time - apply_start_time),'00:00'::interval) - COALESCE(rest_time::interval,'00:00'::interval) - COALESCE(over_rest_time::interval, '00:00'::interval) ) FROM trn_work_time WHERE user_id=10 group by user_id;
★間の秒数を表示
select extract( epoch FROM ('24:00'::interval+ '24:00'::interval )) ;
★秒数を時間に変換して表示
select extract( epoch FROM ('24:00'::interval+ '24:00'::interval ))* interval '1 second';
★秒数の引き算を表示
select extract( epoch FROM ('2009-01-31 00:00'::timestamp - '2009-01-29 00:00'::timestamp));
★サブクエリを使って×
select extract( epoch FROM ( select SUM(COALESCE((apply_end_time - apply_start_time),'00:00'::interval) - COALESCE(rest_time::interval,'00:00'::interval) - COALESCE(over_rest_time::interval, '00:00'::interval) ) FROM trn_work_time WHERE user_id=10 group by user_id ))* interval '1 second';
★サブクエリを使わずに○
select extract( epoch FROM ( SUM(COALESCE((apply_end_time - apply_start_time),'00:00'::interval) - COALESCE(rest_time::interval,'00:00'::interval) - COALESCE(over_rest_time::interval,'00:00'::interval) )))* interval '1 second' FROM trn_work_time WHERE user_id=10 group by user_id;
時間計算2
◆actionで修正
public function getWorkTime($format = 'Y-m-d H:i:s') { $start = $this->apply_start_time;//出勤時間 $end = $this->apply_end_time;//退勤時間 $sum = $end - $start; $sum2 = gdate($format, $sum); $res = $this->rest_time; //休憩時間 $over = $this->over_rest_time;//規定外休憩 }
select apply_end_time - apply_start_time - rest_time - over_rest_time from trn_work_time where id = 番号
【SELECT】
$c = new Criteria(); $c->clearSelectColumns(); $c->addSelectColumn(apply_end_time - apply_start_time - rest_time - over_rest_time); ※どれかの値がNULLなら0を返す。
【WHERE】
$c = new Criteria(); $c->add(id , 40); $c->addSelectColumn(apply_end_time); $c->addSelectColumn(apply_start_time); $c->addSelectColumn(rest_time); $c->addSelectColumn(over_rest_time);
総時間を取得
カスタムSQLを使いその日の勤務時間計を返す
public static function sumWorkTime() { $con = Propel::getConnection(); $query = " select apply_end_time - apply_start_time - rest_time - over_rest_time FROM trn_work_time WHERE id=42"; $sum = $con->prepareStatement($query); $result= $sum->execute(); return populateObjects($result); }
完成版
$c = new Criteria(); $c->addSelectColumn(self::CLINIC_ID); $c->addAsColumn('COUNT' , 'COUNT(*)'); $rs = self::doSelectRS($c); $rs->setFetchMode(ResultSet::FETCHMODE_ASSOC); SELECT SUM(COALESCE((apply_end_time - apply_start_time)::time,'00:00'::time) - COALESCE (rest_time,'00:00'::time) - COALESCE (over_rest_time,'00:00'::time)) as sum_work_time FROM work_time WHERE user_id=10 GROUP BY user_id;
SQLをプロぺルに変換
select extract( epoch FROM ( SUM(COALESCE((apply_end_time - apply_start_time),'00:00'::interval) - COALESCE(rest_time::interval, '00:00'::interval) - COALESCE(over_rest_time::interval,'00:00' ::interval) )))* interval '1 second' FROM trn_work_time WHERE user_id=142 group by user_id;
これをプロペルで表現すると
/** * 総時間を取得 * * @param interval time1 退勤時間 * @param interval time2 出勤時間 * @param interval time3 規定外休憩時間 * @param interval sumtime 総時間(秒数) * @param interval sumtime_hour 総時間 (時間) * * @return interval 総時間を返す */ public static function fetchSumWorkTimeByUserId($userId, $ym) { $time1 = 'COALESCE((' . self::APPLY_END_TIME . ' - ' . self::APPLY_START_TIME . ")::interval, '00:00' ::interval)"; $time2 = 'COALESCE(' .self::REST_TIME . " ::interval, '00:00'::interval)"; $time3 = 'COALESCE(' .self::OVER_REST_TIME . " ::interval, '00:00' ::interval)"; $sumTime = 'SUM(' . $time1 . ' - ' . $time2 . ' - ' . $time3 . ')'; sumTime_hour = 'EXTRACT(EPOCH FROM' . '(' .$sumTime . ') ' . ')' . " * '1 second'::interval"; $c = new Criteria(); $c->clearSelectColumns(); $c->addSelectColumn(self::USER_ID); $c->addAsColumn('SUM_WORK', $sumTime_hour); $c->add(self::USER_ID, $userId, Criteria::EQUAL); $c->addAnd(self::DATE, "DATE_TRUNC('month', " . self::DATE . ") = '" . $ym . "-01'", Criteria::CUSTOM); $c->addGroupByColumn(self::USER_ID); $rs = self::doSelectRS($c); $rs->setFetchMode(ResultSet::FETCHMODE_ASSOC); if ($rs->next()) { $result = $rs->get('sum_work'); } else { $result = '00:00:00'; } return $result; }