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?
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:
DATE() function and the engine cannot use an index on the column.2014-07-11 will be implicitly converted to 2014-07-11 00:00:00 before comparison, which fortunately holds up well with your other explanations.WHERE date < :date + INTERVAL 1 DAY
INTERVAL. Though, when possible, I prefer doing processings the PHP side rather than MySQL or even Apache. I got tired of all their subtleties to manage.
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.
DATE() on indexed colums?
::whereRaw("DATE(date) <= '2014-08-10'"). @Arth He didn't mentioned indexes, so I don't know what's the fuzz about it. @almo 2014-07-10 will be casted to DATETIME (2014-07-10 00:00:00) like you said. However, you're comparing a DATE against a DATETIME, you either cast the DATETIME to DATE or add 23:59:59 to $date.You can get the all record of the date '2016-07-14' or before '2016-07-14' by choosing one syntax from follows:
->whereDate('date','=','2014-07-10')
->whereDate('date', '<=', '2014-07-10')
Or use the another code for dynamic date
whereDate('date',$date)
You can use this
whereDate('date', '=', $date)
If you give whereDate then compare only date from datetime field.
use Carbon\Carbon;
public function scopePublished($query)
{
$now = Carbon::now();
$date = Carbon::parse($now)->toDateString();
$time = Carbon::parse($now)->toTimeString();
return $query->whereDate('published_at', '<', $date)
->orWhere(function($query) use ($date, $time) {
$query->whereDate('published_at', '=', $date)
->whereTime('published_at', '<=', $time);
});
}
If you're still wondering how to solve it.
I use
protected $dates = ['created_at', 'updated_at', 'aired'];
In my model and in my where i do
where('aired', '>=', time());
So just use the unix to compare in where.
In views on the other hand you have to use the date object.
Here is my logic: if you are comparing date then your method should be whereDate and if your comparing complete datetime then your method will be only where:
$calendar_alert = DB::table('calendar_alerts')->whereDate('when', '=', now()->format('Y-m-d'))->where('when', '>', now()->format('H:i:s'))->get();
Facing the same issue, I suggest to read this article : https://dev.to/nicolus/how-to-properly-retrieve-laravel-models-between-two-dates-1bek
Note that the statement :
The issue here is that our created_at column is usually a Datetime, so it's not a simple date but it also has a time. Which means that in practice any post created on the 30th won't be retrieved because their creation date will always be greater than 2021-06-30 (which SQL will assume means '2021-06-30 00:00:00').
... from the article isn't completly true because on my side I tested a direct comparison between a mysql "date" typed field and a string formatted like "2022-10-18", and the issue was the same, the "<=" didn't work right.
