I have the following rule in a model:
[[‘field_date’], ‘datetime’, ‘format’ => ‘php:Y-m-d H:i:s’]
And set value of field_date in code:
$this->field_date = \yii\db\Expression(‘now()’);
Validation fails, getting error that the format in invalid.
How to define rule to allow this?
@cluwong
I am not sure how to use Expression, reading the documentation here, it seems Expression has something to do with query.
Why dont you set the date like this,
$this->field_date = Yii::$app->formatter->asDatetime('now', 'php:Y-m-d H:i:s');If I am not misunderstand, you are trying fetch current datetime from database, but it generates an instance of Express, not a current datetime value, you should execute your query:
$expression = new Expression('NOW()');
$now = (new \yii\db\Query)->select($expression)->scalar(); // SELECT NOW();
$this->field_date = $now;
But I think it is not the right way to do that!
Firstly, it is no need to retrieve current time from database, use PHP instead to avoid increasing database overhead.
Secondly, the time could be affected by the database’s time zone, it will causes some problems if the PHP zone time is different with database time zone.
It is recommended that always use PHP to retrieve datetime, we just need to concern about PHP zone time:
$this->field_date = date('Y-m-d H:i:s');
Expression(“now()”) is used to save the current db datetime into the field. Didn’t use PHP due to timezone issues, as timezone is set based on user’s location.
So there’s no way to define validation to allow both datetime string and Expression(“now()”)?
No, the date validator does not support expression query, it expects datetime string as value.
To answer my own question, I used the ‘when’ attribute to bypass validation when Expression() is used:
[[‘field_date’], ‘datetime’, ‘format’ => ‘php:Y-m-d H:i:s’,
‘when’ => function($model, $attribute) {
return (!$model->$attribute} instanceof \yii\db\Expression);
}]
Why dont use extension like kartik datecontrol ? Then you can save the datetime in GMT and display it according to user’s timezone.
And, does Expression(‘NOW()’) has to be the part of yii\db\Query? I dont understand why the field will have value of dbexpression? Will it be stored as the current datetime when the model is saved? Is it similar as default value in mysql table definition?
Eg, if you want to use database function now() in db query: update table set field_date = now()
\yii\db\Expression(‘now()’) converts that.
I’m using it this way because it’s console command to update the field for selected rows to current database time.
Understand. You use now() because if you use php there is a timezone issue.
So, the workaround is to apply the filter only when the field value is not instance of expression.