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.
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).
