時間計算2

SQL

◆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;//規定外休憩
   }

◆SQL文で修正
数値計算方法
出退勤の時間の差

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なら０を返す。

【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;