Preformat date from database before compare with date from user input in laravel-CodePudding

I have an table where I'm storing downloads of the user, columns - created_at and updated_at. What I'm trying to do is simple filter where I can choose from datepicker From-To dates and show all downloads for given time range.

This is what I have in my function

$dateStart = new DateTime($request->startDate); // '2022-02-01'; 
$dateEnd = new DateTime($request->dateEnd); //'2022-02-08'; 
        //dd($dateStart);
        
$downloadsbyuser = DB::table('downloads')
   ->select('user_id as downloader_id', DB::raw('COUNT(id) as count_by_user_id'))
   ->whereBetween('downloads.updated_at', [$dateStart, $dateEnd])
   ->groupBy('user_id');

So, in database, the columns are defaultfor laravel and timestamp. The date coming from datepicker is in 2022-02-01.

This is what I have in database

ID  user_id   created_at             updated_at
1   34        2022-02-06 10:17:39    2022-02-06 10:17:39
2   34        2022-02-08 10:17:39    2022-02-08 10:17:39
3   20        2022-02-02 10:17:39    2022-02-05 10:17:39
4   34        2022-02-02 10:17:39    2022-02-05 10:17:39

When I search dates 2022-02-01 to 2022-02-06 as you can see I should get 3 results - ID's 1,3 and 4 but instead I've got 2 results. When I search 2022-02-01 to 2022-02-07 then I've got correct 3 results but this means that if there is entry with date 2022-02-07 it wont show.

Why is this happen? Is it because of the whereBetween clause?

CodePudding user response：

Try MySql date_format function!

$dateStart = new DateTime($request->startDate); // '2022-02-01'; 
$dateEnd = new DateTime($request->dateEnd); //'2022-02-08'; 
        
$downloadsbyuser = DB::table('downloads')
   ->select('user_id as downloader_id', DB::raw('COUNT(id) as count_by_user_id'))
   ->whereBetween(DB::raw("DATE_FORMAT(downloads.updated_at, '%Y-%m-%d')"), [$dateStart, $dateEnd])
   ->groupBy('user_id');

CodePudding user response：

Try SQL DATE function

$dateStart = new DateTime($request->startDate); // '2022-02-01'; 
$dateEnd = new DateTime($request->dateEnd); //'2022-02-08'; 
//dd($dateStart);
$downloadsbyuser = DB::table('downloads')
   ->select('user_id as downloader_id', DB::raw('COUNT(id) as count_by_user_id'))
   ->whereBetween('DATE(downloads.updated_at)', [$dateStart->format('Y-m-d'), $dateEnd->format('Y-m-d')])
   ->groupBy('user_id');