No result in \yii\db\Expression when one param is an expression itself

Hello everybody. I have a table with two datetime values and try to fill another field with TIMEDIFF() from those two values. Table structure:


CREATE TABLE IF NOT EXISTS `computer_session` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  ...

  `start_dt` datetime NOT NULL,

  `stop_dt` datetime DEFAULT NULL,

  `duration_seconds` int(11) DEFAULT NULL

) ENGINE=InnoDB  DEFAULT CHARSET=utf8;



duration_seconds calculation shall perform when session is closed. Php code:


class ComputerSession extends \yii\db\ActiveRecord

{

...

	public function startSession() {

		$this->start_dt = new \yii\db\Expression('NOW()');

		$this->save();

	}

	

	public function stopSession() {

		$this->stop_dt = new \yii\db\Expression('NOW()');

		$this->duration_seconds = new \yii\db\Expression('TIMEDIFF(:stop, :start)');

		$this->duration_seconds->params = [

			':start' => $this->start_dt,

			':stop' => $this->stop_dt,

		];

		var_dump($this->save());

	}

}

var_dump($this->save()) in stopSession returns true, stop_dt is correct, but duration_seconds in db is NULL.

I tried to run similar request directly in mysql -


UPDATE `computer_session` SET `stop_dt` = NOW(), `duration_seconds` = TIMEDIFF(`stop_dt`, `start_dt`) WHERE `id` = 1

and


UPDATE `computer_session` SET `stop_dt` = NOW(), `duration_seconds` = TIMEDIFF(NOW(), `start_dt`) WHERE `id` = 1

They work.

I also found a workaround - save the model in controller, find this record again (stop_dt will be filled with datetime value) and call some other method in model that calculates duration_seconds via the same expression. But it’s ugly. Help, please.

Did you check the value of $this->start_dt in stopSession() ? It could be still 'NOW()`. Then TIMEDIFF of NOW() and NOW() should be 0.

But, why don’t you use directly “TIMEDIFF(stop_dt, start_dt)” or “TIMEDIFF(NOW(), start_dt)” as the expression?




    $this->duration_seconds = new \yii\db\Expression('TIMEDIFF(NOW(), `start_dt`)');



No, it is not. There are separate page loads, the first one creates a record, the second one updates it. During the second call, start_dt has some datetime value. And anyway, I have NULL there, not 0.

Um… Yes, it worked! Thanks a lot :)

(It still doesn’t solve the original problem - why yii generates wrong result when an expression is passed as a param to db request. But that’s enough for me right now).

Thanks!

Probably Expression::params doesn’t expect a value to be an Expression object.

The debug toolbar could tell you the actual sql that the expression had produced.