Database calculation with timestamps

Hi everybody,

I am currently trying to map the following query into yii. Unfortunately without success.

SELECT SUM(timestampdiff(SECOND, start, end)) as working_time FROM user_work WHERE user_id=1

The goal is to sum up the total working time of a user. each user has multiple working entries with a start point and an endpoint (typ: timestamps). It is a 1:n relationship.

Who can help me?


you have to define a statistical relation in your model e.g.

return array(

	'workingTime'=>array(self::STAT, 'User_Work', 'select'=>'SUM(timestampdiff(SECOND, start, end))),


Then you could simply query the working time in YourModel or any other Model that is connected via a relation to YourModel e.g.

$workTime = YourModel::model()->with('workingTime')->findAllByPk(1); // or findAllByAttributes, etc...

// calculates the working time from AnotherModel via a relation to YourModel

$workTime2 = AnotherModel::model()->with('relationToYourModel.workingTime')->findAllByPk(1);

not tested and without warranty ;)