Dates have always been a tricky thing to manage in Drupal. Even in PHP. PHP 5.2 introduced the DateTimeInterface which makes handling dates, date ranges, intervals, comparisons etc much easier. However, we always still have the complication of data storage, formatting and different timezones management.

In this article we are going to look at how we can run some entity queries in Drupal 8 using the Date field in our conditions. The requirement for returning entities which have a date field with a value between certain hours is definitely not an edge case, and although seems like an easy task, it can be tricky.

Imagine a simple date field on the Node entity which stores date and time. By default in Drupal 8, the storage for this date is in the format Y-m-d\TH:i:s and the timezone is UTC. However, the site timezone is rarely UTC and we very well may have users choosing their own timezones. So we need to construct our node queries carefully if we want reliable results.

Running a db_query() type of query for returning nodes with the date in a certain range would be a pain at best and impossible at worst. Luckily though, we can, and should always in Drupal 8 try to rely on the entity.query service when looking for entities.

So let's see a couple of examples.

First, an easy one: how do we query for all the nodes which have the field_date value in the future.

$now = new DrupalDateTime('now');
$query = \Drupal::entityQuery('node');
$query->condition('field_date', $now->format(DateTimeItemInterface::DATETIME_STORAGE_FORMAT), '>=');
$results = $query->execute();

A few things to notice. First, we are using the Drupal wrapper of \DateTime and constructing an object to represent our current time. Then we create our entity query and for the date field condition we pass the storage format so that it can be compared to what is being stored. And the regular operators here allow us to find the right entities.

There is one problem with this though. When creating the DrupalDateTime, the site default timezone is used. So if our timezone is not UTC, the query will suffer because we are essentially comparing apples with oranges. And the further away from UTC we are, the more apples start to become compared to cars and airplanes.

To fix this, we need to set the timezone to UTC before running the query.

$now = new DrupalDateTime('now');
$now->setTimezone(new \DateTimeZone(DateTimeItemInterface::STORAGE_TIMEZONE));

And then use $now in the query. The subtle difference to understand is that we are creating $now totally relative to where we are (the site timezone) because we are interested in finding nodes in the future from us, not from from another timezone. However, we then convert it so that we can have them compared properly in the query.

A more complex example could be a range of times. Let's say we want all the nodes with the time of today between 16:00 and 18:00 (a 2 hour span).

I prefer to work directly with \DateTime and then wrap it into the Drupal wrapper just because i can have all the native methods highlighted by my IDE. So we can do something like this:

$timezone = drupal_get_user_timezone();
$start = new \DateTime('now', new \DateTimeZone($timezone));
$start->setTime(16,0);
$start->setTimezone(new \DateTimeZone(DateTimeItemInterface::STORAGE_TIMEZONE));
$start = DrupalDateTime::createFromDateTime($start);

$end = new \DateTime('now', new \DateTimezone($timezone));
$end->setTime(18, 0);
$end->setTimezone(new \DateTimeZone(DateTimeItemInterface::STORAGE_TIMEZONE));
$end = DrupalDateTime::createFromDateTime($end);

$query = \Drupal::entityQuery('node');
$query
  ->condition('field_date', $start->format(DateTimeItemInterface::DATETIME_STORAGE_FORMAT), '>=')
  ->condition('field_date', $end->format(DateTimeItemInterface::DATETIME_STORAGE_FORMAT), '<=');
$results = $query->execute();

So first, we get the user timezone. drupal_get_user_timezone() returns for us the string representation of the timezone the current user has selected, or if they haven't, the site default timezone. Based on that, we create our native date object that represents the current point in time but set the actual time to 16:00. After that we set the storage timezone and create our Drupal wrapper so that we can format it for the query.

For the end date we do the same thing but we set a different time. Then we expectedly write our query conditions and ask for the entities which have a date between those 2 times.

The order of setting the time and timezone on the date object is important. We want to set the time before we set the timezone because the times we are looking for are relative to the current user, not to the storage timezone.

So that is pretty much it. Now you can query for entities and play with date fields without issues (hopefully).