Select Date From DateTime In Query - Yii2

Question

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.

[Refer this.](http://stackoverflow.com/questions/1754411/how-to-select-date-from-datetime-column) — Insane Skull, Aug 08 '16 at 08:50
Do you need to print the date or you need to check in query? — Rajkumar R, Aug 08 '16 at 08:51
$due_date = date('Y-m-d', strtotime($model->due_date)); then pass $due_date into query — vijay nathji, Aug 08 '16 at 08:56
Hi @RajkumarR : I want to print date and even pass this date to find out all the events related to that particular date. — Nana Partykar, Aug 08 '16 at 09:24
@InsaneSkull: Is there any way to select part of date in datetime field in yii2 active query.? — Nana Partykar, Aug 08 '16 at 09:25

score 4 · Accepted Answer · answered Aug 08 '16 at 09:52

4

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();

answered Aug 08 '16 at 09:52

ScaisEdge

131,976
10
91
107

Thank You Scais. This Is What I Was Looking For. – Nana Partykar Aug 08 '16 at 09:58

score 1 · Answer 2 · edited May 04 '19 at 12:12

1

You can simply use this example,

YourModelName::find()->where(['date(timestamp)' => php('Y-m-d')]->one();

Here date will be fetched from timestamp column.

edited May 04 '19 at 12:12

Md.Sukel Ali

2,987
5
22
34

answered May 04 '19 at 10:05

Ankit Singh

922
9
16

vijay nathji · Answer 3 · 2016-08-08T10:01:54.327

0

Here is the query by which you can get the converted date from timestamp using Yii2 model query.

$upcoming_events     = Yii::$app->$db->createCommand('SELECT  count(DISTINCT(date(due_date))) as edate FROM event_master')
    ->queryAll();


    foreach ($upcoming_events as $upevnts){
    echo $upevnts['edate'];
}

edited Aug 08 '16 at 10:01

answered Aug 08 '16 at 09:35

vijay nathji

1,608
13
23

No *vijay* bhai. How to use `select date(due_date)` in yii2 query. This is my question. – Nana Partykar Aug 08 '16 at 09:40

score 0 · Answer 4 · answered Aug 08 '16 at 09:56

I searched for date(due_date) in active query. I didn't get. So, I used Command Builder.

And, It worked.

$upcoming_events_date = Yii::$app->db->createCommand("SELECT DISTINCT date(due_date) FROM events WHERE user_id = :user_id AND is_completed=:is_completed AND  due_date > :due_date ORDER BY due_date ASC LIMIT 0,5")
                                   ->bindParam(':user_id',Yii::$app->users->getId())
                                    ->bindParam(':is_completed',0)
                                    ->bindParam(':due_date',date('Y-m-d'))
                                    ->queryAll();

But, even though it works. I would like to know solution in Active query.

मदद कर.. खुशाल रह.. @Nana – vijay nathji Aug 08 '16 at 10:05 — vijay nathji, Aug 08 '16 at 10:05
Ha Ha `:D` Exactly @vijaynathji – Nana Partykar Aug 08 '16 at 12:13 — Nana Partykar, Aug 08 '16 at 12:13

Select Date From DateTime In Query - Yii2

4 Answers4