DEV Community

loading...
Cover image for How to properly retrieve Laravel models between two dates

How to properly retrieve Laravel models between two dates

nicolus profile image Nicolas Bailly ・4 min read

Let's say you have a blog with posts (or an ad board with listings, or travelers with trips... you get the idea) and you want to retrieve all the posts between two dates. Sounds familiar ? So how exactly do we do that ?


The candid approach

It really seems like a trivial question with an easy (but completely wrong) answer : Just use BETWEEN (or in Laravel whereBetween) like so :

$startDate = '2021-06-01';
$endDate = '2021-06-30';

$posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();
Enter fullscreen mode Exit fullscreen mode

We've all done that (at least I know I have) to retrieve all posts created in June. 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').

Or maybe we're using Carbon and have something like that :

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01');
$endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30');

$posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();
Enter fullscreen mode Exit fullscreen mode

That's actually even worse because it becomes totally unpredictable. A Carbon instance represents an instant, and it has a time too, except if you don't specify a time it will default to the current time when the script runs. So if you run this script at 9AM and the post was created at 8AM on the 30th, you'll retrieve it... But run the exact same script at 7AM, and you won't retrieve that post anymore because $endDate will actually be '2021-06-30 07:00:00'.

We could use $endDate->toDateString() to get rid of the time, but we'd end up in the situation above.


A better way with carbon

One solution would be to make sure that we specify a time in our query, and that this time is at the very start of the day for our start date (00:00:00) and at the very end of the day for our end date (23:59:59.999999).

Fortunately, Carbon provides the startOfDay() and endOfDay() methods that do just that :

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01')->startOfDay();
$endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30')->endOfDay();

$posts = Post::whereBetween('created_at', [$startDate, $endDate])->get();
Enter fullscreen mode Exit fullscreen mode

Now that's much better, we can be pretty sure that everything created on the 1st or the 30th will be retrieved no matter what time they were created at or what time it is.

It's a solid solution and you can definitely use it, but adding a time when really we only care about the date feels a tiny bit like a hack to me, so let's see another solution


A better way with MySQL

We could also explicitly tell MySQL that we only care about the date by using DATE(). The query we want is this :

SELECT * FROM posts
  WHERE DATE(created_at) BETWEEN '2021-06-01' AND '2021-06-30'
Enter fullscreen mode Exit fullscreen mode

That way we'll compare dates with dates, and not with a Datetime. We'll need to resort to DB:raw() to replicate this with Eloquent, which would look like this :

$startDate = '2021-06-01';
$endDate = '2021-06-30';

Post::whereBetween(DB::raw('DATE(created_at)'), [$startDate, $endDate])->get();
Enter fullscreen mode Exit fullscreen mode

Ideally we should make sure that $startDate and $endDate are properly formatted as dates, but it seems to work even if we pass a full Carbon object (which is automatically converted to a string) as MySQL will ignore the time portion.

So that's another way to do it, but I'm not a fan of using DB::raw() either. So let's see a final solution that leverages Eloquent to handle that.


An (even) better way with Eloquent

Eloquent provides a very helpful whereDate() method that will do two things

  1. Build an SQL query that uses the STRFTIME() SQL function to format the content of the column as Y-m-d.
  2. Properly cast a Carbon or Datetime object to the Y-m-d format before comparing it.

Using this, we can confidently pass Carbon instances and know that any time that happens to be a part of it will be discarded and we'll actually be searching between two dates :

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01');
$endDate = Carbon::createFromFormat('Y-m-d', '2021-06-30');

$posts = Post::query()
    ->whereDate('created_at', '>=', $startDate)
    ->whereDate('created_at', '<=', $endDate)
    ->get();
Enter fullscreen mode Exit fullscreen mode

This will generate this SQL query :

SELECT * from "posts"
WHERE STRFTIME('%Y-%m-%d', "created_at") >= cast('2021-06-01' as text)
AND STRFTIME('%Y-%m-%d', "created_at") <= cast('2021-06-30' as text);
Enter fullscreen mode Exit fullscreen mode

And it works flawlessly. The only downside is that we can't use between so it's a little bit longer to write, but if we're going to use it in several places we could easily write a scope (and maybe even make it generic so that it could be imported as a Trait in every model that needs it ?), something like that :

public function scopeCreatedBetweenDates($query, array $dates)
{
    return $query->whereDate('created_at', '>=', $dates[0])
        ->whereDate('created_at', '<=', $dates[1])
}
Enter fullscreen mode Exit fullscreen mode

And use it instead :

$startDate = Carbon::createFromFormat('Y-m-d', '2021-06-01');
$endDate   = Carbon::createFromFormat('Y-m-d', '2021-06-30');

$posts = Post::createdBetweenDates([$startDate, $endDate])->get();
Enter fullscreen mode Exit fullscreen mode

Now that looks pretty good to me !


Conclusion

Dealing with time is hard. I find that when dealing with dates and time, even if it looks simple, it pays to take an extra minute to wonder if it really is that simple and if you're not missing something (and we haven't even touched time zones, Daylight Saving Time, and leap seconds...)

Anything I missed ? Questions ? Horror stories to share about dates ? hit the comments !

Discussion (0)

Forem Open with the Forem app