Laravel's whereBetween not including date from

Question

Note: Code should work for postgres and mysql.

I want to query a table by date_from and date_to. Let's say there is a date range with format: ('d-m-Y')

Foo:

[
 {'id': 1, "created_at": "2017-05-20 11:18:00"},
 {'id': 2, "created_at": "2017-05-24 11:38:36"}
]

Code:

$format = 'd-m-Y';
$df = $request->input('date_from');
$dt = $request->input('date_to');
$date_from = Carbon::createFromFormat($format, $df);
$date_to = Carbon::createFromFormat($format, $dt);

Foo::whereBetween('created_at', [$date_from, $date_to])->get()->toArray();

When I query date_from = '20-05-2017' and date_to = '20-05-2017', I see an empty array. I expect to see 20-05-2017 Foo's regardless. If I date_from = '19-05-2017' and date_to = '21-05-2017', I see 20-05-2017's array.

What's wrong with whereBetween?

Answer 1

It is working as expected. The problem you are encountering is primarily that you are asking for the time between the exact same timestamp. While you are only specifying a specific day and not a time, a time is being included. In this case it is most likely exactly midnight. So '20-05-2017' becomes '20-05-2017 00:00:00'. In this case there are no times except that exact time which will satisfy your whereBetween call.

If you want to get records of a specific date the easiest solution for you would be to use:

$query->whereRaw('date(created_at) = ?', [date('Y-m-d')]);

If you are looking to query between two different dates, your current method will work.

If you want the start time to be the beginning of the day and the end time to be the end of the day you can use Carbon's modifiers for this startOfDay and endOfDay:

$date_from = Carbon::parse($request->input('date_from'))->startOfDay();
$date_to = Carbon::parse($request->input('date_to'))->endOfDay();

$result = Foo::whereDate('created_at', '>=', $date_from)
    ->whereDate('created_at', '<=', $date_to)
    ->get()
    ->toArray();

Answer 2

It is working correctly.

Problem is that you are passing in whereBetween date only Y-m-d format and laravel is comparing this format with Y-m-d H:i:s format of created_at column.

When you are passing only Y-m-d, it will be transformed to Y-m-d 00:00:00. So your created_at 2017-05-20 11:18:00 is not between 20-05-2017 00:00:00 and 20-05-2017 00:00:00.

Answer 3

My startdate and enddate has format YYYY-mm-dd

$query->whereBetween('created_at', [$request->startdate.' 00:00:00', $request->enddate.' 23:59:59']);

Just concatenate time with your date.