DatePicker widget filter a datetime field

Question

Friends, how do I get my DatePicker component below (in Yii2 Framewok), filter a field of type datetime? Since in the component I can only specify the date format.

_search.php file:

<?php

    echo DatePicker::widget([
        'model' => $model,
        'attribute' => 'start_date',
        'attribute2' => 'end_date',
        'language' => 'pt',
        'type' => DatePicker::TYPE_RANGE,
        'separator' => 'até',
        'options' => [
            'placeholder' => '',
        ],
        'pluginOptions' => [
            'autoclose'=>true,
            'todayHighlight' => true,
            'format' => 'yyyy-mm-dd',
        ]
    ]);
?>

UPDATE

public function search($params)
    {
        $query = Report::find();

        $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'sort' => [
            'defaultOrder' => [
                'created' => SORT_DESC, 
                ]
            ],
            'pagination' => [
                'pageSize' => 100,
                ],
        ]);

        $this->load($params);

        if (!$this->validate()) {
            return $dataProvider;
        }

        $query->andFilterWhere([
            'id' => $this->id,
            'created' => $this->created,
            'updated' => $this->updated,
            'closed' => $this->closed,
            'user_id' => $this->user_id,
            'status_id' => $this->status_id,
            'location_id' => $this->location_id,
            'typeperson_id' => $this->typeperson_id,
            'field_cpfcnpj' => $this->field_cpfcnpj,
        ]);

        $query->andFilterWhere(['between', 'created', $this->start_date, $this->end_date]);

        $query->andFilterWhere(['between', 'closed', $this->start_closed, $this->end_closed]);

        return $dataProvider;
    }

Answer 1

If i understand correctly, you want to submit form using the date range which should filter the records using the given range.

Looking at you search() method it looks like you have declared 2 public properties/fields in the search model with the name start_date and end_date which you are using with the DatePicker and you are trying to compare the range with the column created.

You need to do the following in order to filter the records correctly

Make sure for the following

• The start_date and end_date are declared inside the safe rules for the ReportSearch model.

• You need to use the \yii\db\Expression to convert the date in the column to the desired format, and use the php:date to format the given date ranges i.e start_date and end_date.

Add the following before you return the $dataProvider in the search() method

if ($this->start_date !== null && $this->end_date !== null) {
    $query->andFilterWhere(
        [
            'BETWEEN',
            new Expression(
               'DATE_FORMAT(created,"%Y/%m/%d")'
            ),
            date("Y/m/d", strtotime($this->start_date)),
            date("Y/m/d", strtotime($this->end_date)),
        ]
    );
}

Note: if you have the created column saved as timestamp then you need to wrap the field name in the existing query with FROM_UNIXTIME like below, otherwise if the column is of DATE or DATETIME the above will work.

Column type is of TIMESTAMP

if ($this->start_date !== null && $this->end_date !== null) {
    $query->andFilterWhere(
        [
            'BETWEEN',
            new Expression(
                    'DATE_FORMAT(FROM_UNIXTIME(created),"%Y/%m/%d")'
                ),
            date("Y/m/d", strtotime($this->start_date)),
            date("Y/m/d", strtotime($this->end_date)),
        ]
    );
}

Your complete search() method will look like below

public function search($params)
{
    $query = Report::find();

    $dataProvider = new ActiveDataProvider([
        'query' => $query,
        'sort' => [
            'defaultOrder' => [
                'created' => SORT_DESC,
            ]
        ],
        'pagination' => [
            'pageSize' => 100,
        ],
    ]);

    $this->load($params);

    if (!$this->validate()) {
        return $dataProvider;
    }

    $query->andFilterWhere([
        'id' => $this->id,
        'created' => $this->created,
        'updated' => $this->updated,
        'closed' => $this->closed,
        'user_id' => $this->user_id,
        'status_id' => $this->status_id,
        'location_id' => $this->location_id,
        'typeperson_id' => $this->typeperson_id,
        'field_cpfcnpj' => $this->field_cpfcnpj,
    ]);

    if ($this->start_date !== null && $this->end_date !== null) {
        $query->andFilterWhere(
            [
                'BETWEEN',
                new Expression(
                    'DATE_FORMAT(created_at,"%Y/%m/%d")'
                ),
                date("Y/m/d", strtotime($this->start_date)),
                date("Y/m/d", strtotime($this->end_date)),
            ]
        );
    }

    $query->andFilterWhere(['between', 'closed', $this->start_closed, $this->end_closed]);

    return $dataProvider;
}