Date range search in laravel controller-CodePudding

Suppose I have two data rows in my orders table.

`order1 created at 2022-06-18`

`order2 created at 2022-06-19`

Now while I want to search within 2022-06-18 and 2022-06-19 date it shows only order1, but not showing order2 though order2 is also created at 2022-06-19 date.

But If I search within 2022-06-18 and 2022-06-20 then both order1 and order2 row will return as a result.

Here is my laravel query in controller

DB::table('orders')
     ->whereBetween('orders.created_at', [$from_date, $to_date])
     ->get();

Is there any problem? Any body help please?

CodePudding user response：

Between always is inlcuding.

In your case the createdat >= '2022-06-18' and <= '2022-06-20'.

Therefore both rows are included

CodePudding user response：

The default created_at and updated_at "timestamp" field created by laravel migration are of type DateTime not just Date.

When you run

->whereBetween('orders.created_at', ["2022-06-18", "2022-06-19"])

It is equivalent to

->whereBetween('orders.created_at', ["2022-06-18 00:00:00", "2022-06-19 00:00:00"])

But your Order2 has created_at with a different hour/minute/second than "00" on the date "2022-06-19".

That's why you get only one result.

To fix it, either add one day ["2022-06-18", "2022-06-20"] or specify the hour/minute/second.

->whereBetween('orders.created_at', [
    date('Y-m-d 00:00:00', strtotime($from_date)),
    date('Y-m-d 23:59:59', strtotime($to_date)),
])