Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel 5 query builder and NOW() for date comparison

My database (MySQL) has a timestamp field for created_at.

I have two entries, with the timestamps as follows:

  • 2017-12-12 18:25:00
  • 2016-10-02 18:00:00

Today is 2016-10-19 (for future reference). If I use the following query, I get no results:

    $job = $jobs->where('closing_at', '>=', 'NOW()')
            ->orderBy($sorter[0], $sorter[1])->paginate(24);

If I hardcode the date, I get the correct results, just the one future date:

    $job = $jobs->where('closing_at', '>=', "2016-10-19 00:00:00")
            ->orderBy($sorter[0], $sorter[1])->paginate(24);

I'm assuming my error is in the use of NOW() with the Eloquent query builder but I can't figure out the correct way to use this is.

like image 661
TH1981 Avatar asked Oct 19 '16 20:10

TH1981


People also ask

How can we get data between two dates using query in Laravel?

Try to do something like this: $date1 = Carbon::today()->toDateString(); $date2 = Carbon::today()->toDateString(); $myModel = MyModel::find(1); $myModel->whereBetween('created_at', [$date1, $date2]); $myModel->get(); Of course, you will need to change the dates.

How do I use whereBetween in Laravel?

The whereBetween() method is a query builder chained alongside other Laravel query builders used to fetch data from the database. The whereBetween() method queries the database table to fetch rows of records from the database within a range of values.

What is the difference between eloquent and query builder in Laravel?

Eloquent ORM is best suited working with fewer data in a particular table. On the other side, query builder takes less time to handle numerous data whether in one or more tables faster than Eloquent ORM. In my case, I use ELoquent ORM in an application with tables that will hold less than 17500 entries.


1 Answers

Eloquent Query builder doesn't accept those specific SQL functions but as it's done within your PHP you can easily use the great Carbon Class : http://carbon.nesbot.com/docs/ :

use Carbon\Carbon;

And then,

$job = $jobs->where('closing_at', '>=', Carbon::now())
        ->orderBy($sorter[0], $sorter[1])->paginate(24);

That should do the trick.

There is also a whereDate() eloquent method : https://laravel.com/docs/5.3/queries#where-clauses

like image 107
2Fwebd Avatar answered Oct 26 '22 23:10

2Fwebd