Work with time and date specific marco

Published on July 1th, 2019

EDIT: It seems like I was unable to think of the simplest of all solutions would suffice my needs. Turns out a simple where() call does the job - sorry for bothering! You can still read on and take the following as an example on how to use macros!


In one of my recent client projects, I had to query the latest records from the last few hours/minutes. I thought I'd be able to leverage the existing whereDate() method from Laravel's Builder-Class to do the job, but unfortunately it turned out that whereDate() will query all records on a given date ignoring the hours, minutes and seconds. Somehow I thought the method will take the time into account when matching column's value, but it actually makes much more sense that only the date is used for the query. In fact, it's called 'where Date' which makes its functionality pretty obvious. Looking at the documentation it's stated clearly:

The whereDate method may be used to compare a column's value against a date

There exists also a whereTime method, but like the name indicates it only takes the time and not the date into account when looking for matching records. I couldn't find any method which would do both at once, but since the Illuminate\Database\Eloquent\Builder checks for macro calls inside its __call method, I wrote a macro whereDateTime which simply calls whereDate and whereTime methods.

use Illuminate\Database\Eloquent\Builder;

class AppServiceProvider
{
    /**
      * Register any application services.
      */
    public function register()
    {
        Builder::macro('whereDateTime', function ($column, $operator, $value = null, $boolean = 'and') {
            return $this->whereDate($column, $operator, $value, $boolean)
                ->whereTime($column, $operator, $value, $boolean);
        });
    }
}

Having added the macro everywhere I would have to call whereDate and whereTime separately I can now use the whereDateTime right on the Eloquent-Class instance - exactly like the other methods.

Model::whereDateTime('column_name', now()->subMinutes(5))->get()

Thanks for making it so far! I appreciate you taking the time to read through the article. To provide you with higher quality articles I need your feedback on how to write better articles. Did you enjoy the read? Do you have tips on what I could make better? Please tag me on twitter @krishankoenig or write me a mail to krishan.koenig@gmail.com.

Sign up for my newsletter

If you enjoyed this article, sign up for my newsletter to be the first to know when a new article is published.