Yii2 gridview date range picker filter won't function

Question

I have added kartik-v date range picker to my gridview but the filter cannot work after I select the date. The placeholder of the widget will show the selected date but the result in the gridview does not match. The result in the gridview shows all data.

Code in the view :

<?= GridView::widget([
    'options' => ['class' => 'table-sm'],
    'filterModel' => $searchModel,
    'dataProvider' => $dataProvider,
    'summary' =>'',
    'columns' => [
        [
            'attribute' => 'price',
            'value' => function ($model) {
                return number_format($model->price, 3);
            },
            'contentOptions' => ['class' => 'text-right']
        ],
        [
            'attribute' => 'date',
            'contentOptions' => ['class' => 'text-right'],
            'filter' => DateRangePicker::widget([
                'name'=>'date_range_2',
                'presetDropdown'=>true,
                'convertFormat'=>true,
                'includeMonthsFilter'=>true,
                'attribute' => 'date',
                'model' => $searchModel,
                'pluginOptions' => ['locale' => ['format' => 'Y-m-d']],
                'options' => ['placeholder' => 'Select Date']
            ])
        ],
    ]
]); ?>

Below code is my $searchModel part.
Updated:

public function rules()
{
    return [
        [['id'], 'integer'],
        [['name', 'date'], 'safe'],
        [['price'], 'number'],
    ];
}

/**
 * {@inheritdoc}
 */
public function scenarios()
{
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}

/**
 * Creates data provider instance with search query applied
 *
 * @param array $params
 *
 * @return ActiveDataProvider
 */
public function search($params)
{
    $query = Lme::find()->orderBy([
        'date' => SORT_DESC
    ]);

    // add conditions that should always apply here

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort' => false //['defaultOrder' => ['date' => SORT_DESC]]
    ]);

    $this->load($params);

    if (!$this->validate()) {

        return $dataProvider;
    }

    // grid filtering conditions
    $query->andFilterWhere([
        'id' => $this->id,
        'price' => $this->price,
        'date' => $this->date,
    ]);

    $query->andFilterWhere(['like', 'name', $this->name]);


    return $dataProvider;
    }

Answer 1

The DateRangePicker itself gives you a string with the range, so a simple where clause with 'date' => $this->date can not work. Kartik V. provides a behavior kartik\daterange\DateRangeBehavior in the package kartik-v/yii2-date-range.

I'm using it in the following way successfully. Note that my timestamp attribute is timestamp_visit.

In the view:

echo GridView::widget([
...
    'columns' => [
        [
            'attribute' => 'timestamp_visit',
            'format' => [
                'datetime',
                'medium'
            ],
            'width' => '60px',
            'filterType' => GridView::FILTER_DATE_RANGE,
            'filterWidgetOptions' => [
                'model' => $searchModel,
                'convertFormat' => true,
                'pluginOptions' => [
                    'timePicker' => true,
                    'timePickerIncrement' => 1,
                    'timePicker24Hour' => true,
                    'locale' => [
                        'format' => $searchModel::TIME_FORMAT,
                    ]
                ]
            ]
        ],
    ...
    ]

In the search model:

<?php
...
use kartik\daterange\DateRangeBehavior;

class ...Search extends ....
{
    const TIME_FORMAT = 'Y-m-d H:i:s';

    /**
     * @var string Start date filled up by DateRangeBehavior
     */     
    public $timestampVisitStart;

    /**         
     * @var string End date filled up by DateRangeBehavior
     */         
    public $timestampVisitEnd;
...

       public function rules()
    {
        return [
            [['timestamp_visit'], 'match', 'pattern' => '/^.+\s\-\s.+$/'],
            ...
        ];
    } 
    public function behaviors()
    {
        return [
            [
                'class' => DateRangeBehavior::class,
                'attribute' => 'timestamp_visit',
                'dateStartAttribute' => 'timestampVisitStart',
                'dateEndAttribute' => 'timestampVisitEnd',
                'dateStartFormat' => self::TIME_FORMAT,
                'dateEndFormat' => self::TIME_FORMAT,
            ]
        ];
    }
...
    public function search($params)
    {
        $query = self::find();

        $dataProvider = new ActiveDataProvider([
            'query' => $query
        ]);

        $dataProvider->sort = new Sort([
            'defaultOrder' => [
                'timestamp_visit' => SORT_DESC,
                'id' => SORT_DESC,
            ]
        ]);
...
        if ($this->timestampVisitStart) {
            $query->andWhere(['>=', "timestamp_visit", $this->timeToUTC($this->timestampVisitStart)]);
        }
        if ($this->timestampVisitStart) {
            $query->andWhere(['<=', "timestamp_visit", $this->timeToUTC($this->timestampVisitEnd)]);
        }

        return $dataProvider;
   }

   /**
     * Convert string time in format $format to UTC time format for SQL where clause
     * @param string $time Time in format $format
     * @param string $format Format of $time for the function date, default 'Y-m-d H:i:s'
     */
    private function timeToUTC($time, $format='Y-m-d H:i:s')
    {
        $timezoneOffset = \Yii::$app->formatter->asDatetime('now', 'php:O');
        return date($format, strtotime($time.$timezoneOffset));
    }
}