Yii2 - gridview filter unix timestamp

Question

I am confused about filtering fields like created_at or updated_at in GridView. Yii2 suggests to use unix timestamp saved into integer field in MySQL database. It's all fine but how can I filter those values? If I want to filter only date - do I have to add sth like the following to the search method?

$query->andFilterWhere([
'DATE(FROM_UNIXTIME('.$this->tableName() . '.created_at))' => $this->created_at]);

Or use FROM_UNIXTIME with specific format? Sure it can be done like that but comparing the field means converting all values to date and then compare.

Am I doing it wrong?

Answer 1

You are not doing it wrong but if you want to filter on the timestamp you have at least two options.

1. Convert the timestamp within the query.
2. Convert the value you want to filter on.

Your remark that it can be done with converting all values to date and than compare it, is the first option (MySql convert every time stamp to a date). (But don't you have to convert $this->created_at to a date as well?).

With the second option, you convert the $this->created_at value to a lowest and highest unix timestamp value and use a SELECT BETWEEN filter clause. In that case you need only two conversions that are done by Php (conversion to lowest and highest date value) and MySql just performs 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]);