Select Number of Rows in Date Range via Query Builder?

Greetings.

So, I have this query which selects the number of rows in the past 30 days:


            SELECT COUNT( DATE_FORMAT( created_at, '%m/%d/%Y' ) )
            FROM    enc_encounters
            WHERE   created_at BETWEEN NOW() - INTERVAL 30 DAY AND NOW()";

I am trying to recreate this via query builder as such:


        $result = $this->select('DATE_FORMAT( created_at, \'%m/%d/%Y\' )')->whereBetween('created_at', ['NOW() - INTERVAL 30 DAY', 'NOW()'] )->count();

I am receiving a result of 0 (It is meant to return 3).

Any idea on where I am going wrong?

xdega

9 years ago

Best Answer

Level 4

Well. With a little bit of diligence, I figured out a solution with the help of Carbon.

To help anyone else who may have a similar issue here is my much cleaner result:

    public function getPastMonth(){

        $now = Carbon::now();

        $result = $this->whereYear('created_at',$now->year)->whereMonth('created_at',$now->month)->count();

        return $result;

    }

Note: This is just a function in the Model file.

Please or to participate in this conversation.