Query records from the last 7 days [duplicate]

Question

Today is 2/24/2022

On my report page, I read interval from the query params, and this is what I have in my controller:

$inputs    = Request::all();
$interval  = 'week'; // <<------------ Default Value 

if(array_key_exists('interval', $inputs)){
    $interval  = $inputs['interval'];
}


switch ($interval) {
    case 'day':
    $q = BabyLog::where('updated_at', '>', now()->today());
    break;
    case 'week':
    $q = BabyLog::where('updated_at', '>', now()->subWeek());
    break;
    case 'month':
    $q = BabyLog::where('updated_at', '>', now()->subMonth());
    break;
    case 'year':
    $q = BabyLog::where('updated_at', '>', now()->subYear());
    break;
    default:
    $q = BabyLog::orderBy('updated_at', 'desc');
    break;
}




$logs = $q->where('babyId',$baby->id)->orderBy('updated_at', 'desc')->get()->groupBy(function ($log) {
    return $log->updated_at->format('Y-m-d');
});

dd($logs);

return

Illuminate\Database\Eloquent\Collection {#344 ▼
  #items: array:8 [▼
    "2022-02-24" => Illuminate\Database\Eloquent\Collection {#352 ▶}
    "2022-02-23" => Illuminate\Database\Eloquent\Collection {#353 ▶}
    "2022-02-22" => Illuminate\Database\Eloquent\Collection {#351 ▶}
    "2022-02-21" => Illuminate\Database\Eloquent\Collection {#349 ▶}
    "2022-02-20" => Illuminate\Database\Eloquent\Collection {#350 ▶}
    "2022-02-19" => Illuminate\Database\Eloquent\Collection {#348 ▶}
    "2022-02-18" => Illuminate\Database\Eloquent\Collection {#346 ▶}
    "2022-02-17" => Illuminate\Database\Eloquent\Collection {#345 ▶}
  ]
}

I only want to display the last 7 days on my graph.

1. Why does 2022-02-17 is also on the list ??

2. What did I do wrong on the above codes?

3. Does subWeek() always return 8 ?

4. Should I just do subWeek() -1 ?

But ... subMonth(), and subYear() I don't have to do it.

Answer 1

You are wrong about that, you dont have to do it in case of Month and year, it has same behavior. subWeek() substracts one whole week(same behavior for month and year), so if you substract a whole week from date 2022-02-24, you are getting final answer of 2022-02-17 (24-7=17)

Solution
changed '>' to '<'

case 'week':
    $q = BabyLog::where('updated_at', '<', now()->subWeek());
break;

Other possible solution could be whereBetween()

$q->whereBetween('created_at', [
    Carbon::now()->subWeek()->startOfWeek(),
    Carbon::now()->subWeek()->endOfWeek(),
]);

Also possible duplicate for question: https://stackoverflow.com/a/45387484/16833260

Answer 2

Solution 1

You could try to use subDays(7) instead.

case 'week':
    $q = BabyLog::where('updated_at', '>', now()->subDays(7));
break; 

Solution 2

Using DB::raw.

case 'week':
    $q = BabyLog::where('updated_at', '>', DB::raw('NOW() - INTERVAL 1 WEEK'));
break;