Laravel Eloquent Compare Date from Datetime Field

Laravel Eloquent compare date from datetime field

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.

Compare date time in Laravel eloquent

You have to invert the >= and <= signs

$date = date('Y-m-d H:i:s', strtotime($request->date));
$contact_number = $request->contact_number;

$event = Event::where(function ($query) use ($date, $contact_number){
$query->where('start_date', '<=', $date)
->where('end_date', '>=', $date);
})
->where('contact',$contact_number)
->paginate(5);

How to compare simple date with datetime in Laravel?

you can use eloquent whereDate with format Y-m-d

YourModel::whereDate('created_at','2021-11-24')->get();

or without model

DB::table('your_table')->whereDate('created_at','2021-11-24')->get();

Laravel Eloquent compare dates by specific format

Convert your column to a date format, such as DATE, and then it will work as intended.

How to use a WHERE on eloquent for comparing a date created by year and month column in a table for Laravel?

I think that calculation is too expensive.
When you give year and month there can be only two comparations.

  1. If year is greater than given, then date is definitely greater than given date
  2. If year is same but month is greater or equal.

So I think the correct query should be:

public function scopeStartsFrom(Builder $query, $month, $year): Builder
{
return $query->addSelect(
DB::raw("str_to_date(concat(year,'-',month,'-',1), '%Y-%m-%d') as carbon")
)->where(function($q) use ($year, $month) {
$q->where('year', '>', $year)
->orWhere([
['year', '>=', $year],
['month', '>=', $month]
]);
});

// Generated query:
SELECT * FROM table WHERE (year > 2022 OR (year >= 2022 AND month >= 4))
}

Laravel/Eloquent and comparing dates

The answer that user1977808 gave you is not good because MySQL can't use an index on the timestamp column, since it has to compute an output of the DATE_SUB function for every row. Avoid such queries, they have to process the entire table every time!

How about something like this:

return MainContact::where('timestamp', '>=', time() - (24*60*60))->get();

I put the >= in there because you said "a day or less old", so they must have timestamp that is later than yesterday.

Laravel - where less/greater than date syntax

Use a Carbon instance:

$this->data['Tasks'] = \DB::table('tb_tasks')->where('Status', 'like', 'Open%')->whereDate('DeadLine', '>', Carbon::now())->count();

You can also use the now() helper

$this->data['Tasks'] = \DB::table('tb_tasks')->where('Status', 'like', 'Open%')->whereDate('DeadLine', '>', now())->count();


Related Topics



Leave a reply



Submit