Querying Against Dates and Timezones with Eloquent

In the previous blog, we saw how to efficiently build and run an SQL query that takes into account the user's timezone. In this blog, we will see how we can use Laravel's Eloquent with Carbon to convert the dates easily.

Everyone Loves Carbo(n)hydrate 🥖

In the previous blog, we ended up with the following query that let the database use the index on the created_at column:

select `name`
from `events`
where `user_id` = 1
and `created_at`
between convert_tz('2017-12-28 00:00:00', "+10:00", "+00:00") and convert_tz('2017-12-29 23:59:59', "+10:00", "+00:00");

The +10:00 represents the timezone we used for the user.

Right now, the timezone is hard-coded in the query and if we want to make it dynamic, we would have to nest some select queries in there or use another scripting language such as PHP or Javascript to prefetch the user's details needed for the query:

select `name`
from `events`
where `user_id` = 1
and `created_at`
between
    convert_tz('2017-12-28 00:00:00', (select `timezone` from `users` where `id` = 1), "+00:00")
    and convert_tz('2017-12-29 23:59:59', (select `timezone` from `users` where `id` = 1), "+00:00");

Using Eloquent, we would have an Event model and a User model in our system. The query above can be structured like so:

Event::where('user_id', $user->id)
    ->whereBetween('created_at', [$startDate, $endDate])
    ->get('name');

All that's left is to fill in the $user, the $startDate, and the $endDate. The $user may be the authenticated user or any other user in our system.

If you remember from the previous blog, we saw that we need to apply the timezone transformation to the arguments rather than the created_at column itself. To make the calculation of the $startDate and $endDate super simple, we can use Carbon. As it turns out, Laravel ships with that package already installed as it uses it heavily in its code base. In fact, Laravel also has its own Carbon class that extends the base Carbon\Carbon class: Illuminate\Support\Carbon. Laravel's Carbon object is implementing the Macroable trait, and offers a jsonSerialize() method as well as a custom serializeUsing() method. We won't cover them as they are out of scope for this blog.

If the user wants to get all events that took place in his account between December 28, 2017, and December 29, 2017, in his timezone (+10:00 in this example), the calculation of our start and end dates can be as simple as:

use Illuminate\Support\Carbon;

$startDate = Carbon::parse('2017-12-28 00:00:00', $user->timezone)->setTimezone('UTC');
$endDate = Carbon::parse('2017-12-29 23:59:59', $user->timezone)->setTimezone('UTC');

You can pass Carbon objects to Eloquent Models. Eloquent knows how to handle Carbon objects,

Putting it all together:

use Illuminate\Support\Carbon;

$user = Auth::user(); // Perform any needed logic to fetch the user.
$startDate = Carbon::parse('2017-12-28 00:00:00', $user->timezone)->setTimezone('UTC');
$endDate = Carbon::parse('2017-12-29 23:59:59', $user->timezone)->setTimezone('UTC');

Event::where('user_id', $user->id)
    ->whereBetween('created_at', [$startDate, $endDate])
    ->get('name');

If you have an events relationship on your User model, you may even make it prettier:

use Illuminate\Support\Carbon;

$user = Auth::user(); // Perform any needed logic to fetch the user.
$startDate = Carbon::parse('2017-12-28 00:00:00', $user->timezone)->setTimezone('UTC');
$endDate = Carbon::parse('2017-12-29 23:59:59', $user->timezone)->setTimezone('UTC');

$user->events()
    ->whereBetween('created_at', [$startDate, $endDate])
    ->get('name');

What we do is we tell Carbon to parse the given date and telling it that the given date is in the $user->timezone timezone. The timezone may be a string such as +10:00 or any string in the official PHP supported timezones page. Then we tell Carbon to convert that date to the UTC timezone, the same as our database. Simple, right?