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?

What is the type of $this->created_at? Is that also a unix timestamp? — Barry
– Barry, Commented Oct 27, 2014 at 17:50
PHP sees it as an integer, in database it is also a simple integer so I can save unix timestamp. — Joe
– Joe, Commented Oct 28, 2014 at 9:50

mae · Accepted Answer · 2017-01-05 23:39:34Z

4

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

Convert the timestamp within the query.
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]);

edited Jan 5, 2017 at 23:39

mae

15.8k8 gold badges36 silver badges49 bronze badges

answered Oct 27, 2014 at 18:41

Barry

3,7331 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joe Over a year ago

Thank you. That was really helpful. I made a trait using your code and use it in inside of my query.

Collectives™ on Stack Overflow

Yii2 - gridview filter unix timestamp

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related