Yii2 - gridview filter timestamp
I am confused about filtering type fields created_at
or updated_at
in a GridView. Yii2 suggests using a unix timestamp stored in an integer field in a MySQL database. Everything is fine, but how can I filter these values? If I want to filter only the date - do I need to add sth to the search method like below?
$query->andFilterWhere([
'DATE(FROM_UNIXTIME('.$this->tableName() . '.created_at))' => $this->created_at]);
Or use FROM_UNIXTIME
with a specific format? Of course it can be done like this, but comparing a field means converting all the values to date and then comparing.
Am I doing it wrong?
source to share
You are not doing it wrong, but if you want to filter out the timestamp, you have at least two options.
- Convert timestamp in request.
- Convert the value you want to filter.
Your point that this can be done with converting all values to date rather than comparing them is the first option (MySql converts each timestamp to a date). (But you don't need to convert $this->created_at
to date?).
With the second option, you convert the value $this->created_at
to the lowest and highest unix timestamp value and use the SELECT BETWEEN filter clause. In this case, you only need two conversions, which are done with Php (conversion to lowest and highest date value) and MySql just does a range check.
// date to search
$date = DateTime::createFromFormat('m/d/Y', '10/27/2014');
$date->setTime(0,0,0);
// set lowest date value
$unixDateStart = $date->getTimeStamp();
// add 1 day and subtract 1 second
$date->add(new DateInterval('P1D'));
$date->sub(new DateInterval('PT1S'));
// set highest date value
$unixDateEnd = $date->getTimeStamp();
$query->andFilterWhere(
['between', 'created_at', $unixDateStart, $unixDateEnd]);
source to share