Nana Partykar Nana Partykar - 3 months ago 106
PHP Question

Select Date From DateTime In Query - Yii2

I'm having one event table. In which,

due date
of event is being stored in
datetime
format. But, due to some change of requirement, now we need to show only
date
(excluding time) from
due date
column.

Event (Table)

id | user_id | description | due_date | is_completed

1 8 My Event1 2016-08-09 19:16:00 0
2 8 My Event2 2016-08-09 19:53:00 0


I wanted to show all event in date wise. Like all event under
2016-08-09
.

So, I tried with this query.

$upcoming_events = Events::find()->select(['due_date'])->distinct()
->where(['user_id' => Yii::$app->users->getId(),'is_completed'=> 0 ])
->andWhere(['>=','due_date',date("Y-m-d")])
->orderBy(['due_date'=>'ASC'])->limit(5)->all();


But, now 2 dates are being selected as
2016-08-09 19:16:00
&
2016-08-09 19:53:00
. Since, date part is not being fetched from select statement. It is showing 2 times same date.

var_dump($upcoming_events);

[1] => app\Events Object
(
[_attributes:yii\db\BaseActiveRecord:private] => Array
(
[due_date] => 2016-08-09 19:16:00
)
)

[2] => app\Events Object
(

[_attributes:yii\db\BaseActiveRecord:private] => Array
(
[due_date] => 2016-08-09 19:53:00
)
)


How can I retrieve only date from date time field to get only 1 date in Yii2 Query.

Any help/hint/suggestions is appreciable.

Answer

You can also use only the date part

 upcoming_events = Events::find()->select('date(due_date) as due_date')->distinct()
       ->where(['user_id' => Yii::$app->users->getId(),'is_completed'=> 0 ])
       ->andWhere(['>=','due_date',date("Y-m-d")])
      ->orderBy(['due_date'=>'ASC'])->limit(5)->all();
Comments