Laravel Eloquent compare date from datetime field

I want to get all the rows from a table through an expression:

table.date <= 2014-07-10

But if the column contains a datetime let's say:

2014-07-10 12:00:00

But if I do:

where('date', '<=', $date)

it won't get the row.

I guess this is because $date = 2014-07-10 which makes MySQL assume that it is 2014-07-10 00:00:00.

In regular MySQL I would just do

where DATE(date) <= $date

What would be the equivalent using Laravel's Eloquent?

Solution 1:

Laravel 4+ offers you these methods: whereDay(), whereMonth(), whereYear() (#3946) and whereDate() (#6879).

They do the SQL DATE() work for you, and manage the differences of SQLite.

Your result can be achieved as so:

->whereDate('date', '<=', '2014-07-10')

For more examples, see first message of #3946 and this Laravel Daily article.

Update: Though the above method is convenient, as noted by Arth it is inefficient on large datasets, because the DATE() SQL function has to be applied on each record, thus discarding the possible index.

Here are some ways to make the comparison (but please read notes below):

->where('date', '<=', '2014-07-10 23:59:59')

->where('date', '<', '2014-07-11')

// '2014-07-11'
$dayAfter = (new DateTime('2014-07-10'))->modify('+1 day')->format('Y-m-d');

->where('date', '<', $dayAfter)

Notes:

• 23:59:59 is okay (for now) because of the 1-second precision, but have a look at this article: 23:59:59 is not the end of the day. No, really!
• Keep in mind the "zero date" case ("0000-00-00 00:00:00"). Though, these "zero dates" should be avoided, they are source of so many problems. Better make the field nullable if needed.

Solution 2:

Have you considered using:

where('date', '<', '2014-08-11')

You should avoid using the DATE() function on indexed columns in MySQL, as this prevents the engine from using the index.

UPDATE

As there seems to be some disagreement about the importance of DATE() and indexes, I have created a fiddle that demonstrates the difference, see POSSIBLE KEYS.