Laravel get entries in the database in given interval of time

Question

I want to display in the admin panel statitics about users and other things in the database. For example to show how many users were registered today, this month etc. For now I am doing this with the users for today the following way:

$users = User::where('admin', 0)->get();

    $usersRegisteredToday = array_filter($users->toArray(), function ($user) {
        $registerDate = \DateTime::createFromFormat('Y-m-d H:i:s', $user['created_at']);
        $registerDate->setTime(0,0,0);
        $today = new \DateTime();
        $today->setTime(0,0,0);

        $diff = $today->diff($registerDate);
        $diffDays = (integer)$diff->format("%R%a"); // Extract days count in interval
        return  $diffDays == 0;
    });

    return view('admin.index', compact("users", "usersRegisteredToday"));

And in the view:

<p class="text-no">Today: {{ count($usersRegisteredToday) }} </p>

I wonder if there is a better, simpler and faster way to do this, because I think if I get the information for the other things that way it will be very slow and heavy. So i want to know the best and lightest way to do this.

Waqas · Accepted Answer · 2018-09-13T15:40:09.177

1

As of Laravel 5.3 we can use whereDate / whereMonth / whereDay / whereYear

For example to get records created today:

$users = User::whereDate('created_at', DB::raw('CURDATE()'))->get();

Possibly a similar question is asked here: Get only records created today in laravel

edited Sep 13 '18 at 15:40

answered May 02 '17 at 22:54

Waqas

714
5
13

Thanks, I had figured it out and I was typing it as an answer to my own question but you were faster. – Angel Miladinov May 02 '17 at 22:57
I am happy that you figured it out yourself. Good luck with your coding :) – Waqas May 03 '17 at 23:51

score 0 · Answer 2 · answered May 02 '17 at 23:00

$todayStart = (new Carbon())->setTime(0, 0);
$todayEnd = (new Carbon())->setTime(23, 59, 59);
$users = User::where('admin', 0)
             ->where('created_at', '>=', $todayStart)
             ->where('created_at', '<=', $todayEnd)                     
             ->get();

You could use query scopes which would make it even better. Inside User model:

class User extends Model
{
    public function scopeCreatedBefore(Carbon $date)
    {
        return $this->where('created_at', '<=', $date);
    }

    public function scopeCreatedAfter(Carbon $date)
    {
        return $this->where('created_at', '>=', $date);
    }
}

And for the usage:

$todayStart = (new Carbon())->setTime(0, 0);
$todayEnd = (new Carbon())->setTime(23, 59, 59);
$users = User::where('admin', 0)
             ->createdAfter($todayStart)
             ->createdBefore($todayEnd)                     
             ->get();

score 0 · Answer 3 · answered Jun 22 '18 at 08:42

Its quite easy to do it. Im doing this in laravel 5.6 We are working in controller here. Lets say $date1 and $date2 is your range. First, parse the date to carbon

 $olddate = Carbon::parse($date1)->format('Y-m-d');
 $newdate = Carbon::parse($date2)->format('Y-m-d');

Now, we get the users within that date range.

 $users = Users::latest()
     ->whereBetween('created_at', array($olddate, $newdate))
     ->orderBy('id', 'desc')
     ->get(); // or count if you just want to count

basically, the function will look like below

public function betweendates(Request $request)
{
 // get dates from request
 $date1= $request->olddate;
 $date2= $request->newdate;

// parse date to carbon
 $olddate = Carbon::parse($date1)->format('Y-m-d');
 $newdate = Carbon::parse($date2)->format('Y-m-d');

 // the magic stuff
 $users = Users::latest()
     ->whereBetween('created_at', array($olddate, $newdate))
     ->orderBy('id', 'desc')
     ->count();

    return view('users.dashboard', compact('users'));

 }

Laravel get entries in the database in given interval of time

3 Answers3