How to filter unix timestamp in Yii2 GridView?

Question

I am using Kartik GridView and my date column setup is the following

    [
        'attribute'=>'created_date',
            'value' => function ($model, $index, $widget) {
            return Yii::$app->formatter->asDate($model->created_date);
        },
        'format' => ['date', 'php:d.m.Y'],
        'filterType' => GridView::FILTER_DATE,
        'filterWidgetOptions' => [
        'pluginOptions' => [
            'format' => 'yyyy-mm-dd',
            'autoclose' => true,
            'todayHighlight' => true,
        ]
        ],
        'width' => '160px',
        'hAlign' => 'center',
    ],

Search model

[['created_date'], 'safe'],

$query->andFilterWhere([
            'links_id' => $this->links_id,
            'modified_date' => $this->modified_date,
            'created_date' => $this->created_date,
        ]);

created_date is the column in the database that is generated automatically when record is created, which has the following format 2015-12-16 13:42:09.425618.

Although date picker works I get SQL Error:

The SQL being executed was: SELECT COUNT(*) FROM "links" LEFT JOIN "countries" ON "links"."country" = "countries"."country" WHERE "created_date"='2015-12-15'

So, how to properly filter unix timestamp with Yii2 kartik date picker?

@ck_arjun Ambiguous column: 7 ERROR: column reference "created_date" is ambiguous — Kirill Ryzhkov
– Kirill Ryzhkov, Commented Dec 22, 2015 at 11:32
Your links table and countries table.If both have "created_date" field you should specify table name along with field , wherever applicable. "links.created_date" => $this->created_date — ck_arjun
– ck_arjun, Commented Dec 23, 2015 at 3:47

Kirill Ryzhkov · Accepted Answer · 2015-12-23 08:11:10Z

1

Ok, thanks @ck_arjun. I figured this out. So in the search model the code should look like this

// Convert date to Unix timestamp
if (!empty($params['LinksSearch']['created_date'])) {
      $query->andFilterWhere([
        '(links.created_date::DATE)' => date('Y-m-d',strtotime($params['LinksSearch']['created_date']))
      ]);
 }

This way you will be able to pass date in you own format to filter unix database column.

answered Dec 23, 2015 at 8:11

Kirill Ryzhkov

5222 gold badges9 silver badges23 bronze badges

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

Comments

Amitesh Kumar · Accepted Answer · 2015-12-22 11:14:37Z

0

try this in your grid view :

 [
        'attribute'=>'created_date',
            'value' => function ($model, $index, $widget) {
           $date = date('your date format eg.Y-m-d H:m:s', strtotime($model->created_date);
            return date ;
        },
        'format' => ['date', 'php:d.m.Y'],
        'filterType' => GridView::FILTER_DATE,
        'filterWidgetOptions' => [
        'pluginOptions' => [
            'format' => 'yyyy-mm-dd',
            'autoclose' => true,
            'todayHighlight' => true,
        ]
        ],
        'width' => '160px',
        'hAlign' => 'center',
    ],

answered Dec 22, 2015 at 11:14

Amitesh Kumar

3,0891 gold badge28 silver badges42 bronze badges

1 Comment

Kirill Ryzhkov Over a year ago

Tried it but still get this error

The SQL being executed was: SELECT COUNT(*) FROM "links" LEFT JOIN "countries" ON "links"."country" = "countries"."country" WHERE "created_date"='2015-12-15'

. Also tried this solution stackoverflow.com/questions/28317351/… but getting

The SQL being executed was: SELECT COUNT(*) FROM "links" LEFT JOIN "countries" ON "links"."country" = "countries"."country" WHERE "created_date"=1450137600

Anton Sanzharovskyi · Accepted Answer · 2018-03-03 10:20:07Z

0

In search model before return $dataProvider

insert code like next:

    if($this->created_normal)
    $query->andFilterWhere(['between', 'created_at', strtotime($this->created_normal), strtotime($this->created_normal)+86400]);
if($this->updated_normal)
    $query->andFilterWhere(['between', 'updated_at', strtotime($this->updated_normal), strtotime($this->updated_normal)+86400]);

updated_normal - help field in search model (yyyy-mm-dd)

Working is guaranted for int

answered Mar 3, 2018 at 10:20

Anton Sanzharovskyi

1

Collectives™ on Stack Overflow

How to filter unix timestamp in Yii2 GridView?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related