Date (range) fields and Entity Query [Update]

Ever wanted to add a condition on the value of a date field on an entity query in Drupal 8? Here is how it works reliably.

For Drupal 8.5.x and later:

use Drupal\Core\Datetime\DrupalDateTime;
use Drupal\datetime\Plugin\Field\FieldType\DateTimeItemInterface;
 
// Get a date string suitable for use with entity query.
$date = new DrupalDateTime();
$date->setTimezone(new \DateTimeZone(DateTimeItemInterface::STORAGE_TIMEZONE));
$date = $date->format(DateTimeItemInterface::DATETIME_STORAGE_FORMAT);
 
// Set the condition.
$query->condition('field_date.value', $date, '>');

For Drupal 8.4.x and earlier:

use Drupal\Core\Datetime\DrupalDateTime;
 
// Get a date string suitable for use with entity query.
$date = new DrupalDateTime();
$date->setTimezone(new \DateTimeZone(DATETIME_STORAGE_TIMEZONE));
$date = $date->format(DATETIME_DATETIME_STORAGE_FORMAT);
 
// Set the condition.
$query->condition('field_date.value', $date, '>');

The trick is to use a DrupalDateTime object, on which we set the proper time zone used by the storage (provided by the constant DATETIME_STORAGE_TIMEZONE) and format that in the proper date format used by the storage (provided by the constant DATETIME_DATETIME_STORAGE_FORMAT). That way, we'll get reliable results. In the example above I just wanted to filter by the current time, but you can pass a date string to the constructor of DrupalDateTime as well, if you want something else.

Please note: Starting with Drupal 8.5.x, the constants mentioned above have been deprecated in favor of constants on a new DateTimeItemInterface. They will be removed in Drupal 9.x.

You can also get these objects directly from a date field's value by using getDateTime():

foreach ($node->field_date_range as $item) {
  foreach ($item as $index => $value) {
    $date = $value->getDateTime();
  }
}

If you're using experimental date range fields, another thing you need to know is, that the end date is stored in an end_value column.

Here's a more complete example querying for event nodes in the future:

use Drupal\Core\Datetime\DrupalDateTime;
use Drupal\Core\Language\LanguageInterface;
 
// @note: In Drupal 8.5.x and later also include the following line:
use Drupal\datetime\Plugin\Field\FieldType\DateTimeItemInterface;
 
$storage = \Drupal::service('entity_type.manager')->getStorage('node');
$query = $storage->getQuery()
  ->condition('type', 'event')
  ->condition('status', 1)
  ->sort('field_date_range.value', 'ASC')
  ->sort('field_date_range.end_value', 'ASC')
  ->sort('created', 'ASC');
 
$query->condition('langcode', [
  \Drupal::service('language_manager')->getCurrentLanguage()->getId(),
  LanguageInterface::LANGCODE_NOT_APPLICABLE,
  LanguageInterface::LANGCODE_NOT_SPECIFIED,
], 'IN');
 
// @note BEGIN: Code for Drupal 8.5.x and later: 
$date = new DrupalDateTime(); 
$date->setTimezone(new \DateTimeZone(DateTimeItemInterface::STORAGE_TIMEZONE)); 
$date = $date->format(DateTimeItemInterface::DATETIME_STORAGE_FORMAT);
// @node END: Code for Drupal 8.5.x and later.
 
// @note BEGIN: Code for Drupal 8.4.x and earlier:
$date = new DrupalDateTime();
$date->setTimezone(new \DateTimeZone(DATETIME_STORAGE_TIMEZONE));
$date = $date->format(DATETIME_DATETIME_STORAGE_FORMAT);
// @node END: Code for Drupal 8.4.x and earlier.
 
$and = $query->andConditionGroup()
  ->condition('field_date_range.value', $date, '>')
  ->notExists('field_date_range.end_value');
 
$or = $query->orConditionGroup()
  ->condition($and)
  ->condition('field_date_range.end_value', $date, '>');
 
$query->condition($or);
 
$ids = $query->execute();
$nodes = $storage->loadMultiple($ids);

Note, that there's currently an issue in Drupal Core with nested condition groups in entity query, which are used in the example above.

Update: This post has been updated to reflect changes in Drupal 8.5.x (see Global constants in datetime.module are deprecated and DateTimeItemInterface has been introduced).

Systems

Drupal 8/9