How to do a date range entityQuery with a Date only field in Drupal 8?

Question

In Drupal 8, the Date only field stores data in a varchar column in a CCYY-MM-DD format, e.g., 2016-04-18.

As such, you can't simply do a greater than (>=), less than (<=) or similar timestamp based entityQuery anymore.

Is there a simple solution on how to deal with this? Specifically I am attempting to do two queries, one where the date field is between 14 and 21 days old, and one where the date field is 21 and over days old.

Answer 1

I'm doing lots of conditions like that in entity queries, I haven't encountered a problem yet.

The main thing you have to be aware of is the timezone the data is stored, which is UTC. You need to convert it to the storage timezone (there's a constant for that), or your query will be off by a few hours.

Here's an example based on something I'm doing, if you only store days, then there is a constant for formatting that too.

$date = new DrupalDateTime('21 days ago');
$date->setTimezone(new \DateTimezone(DATETIME_STORAGE_TIMEZONE));
$formatted = $date->format(DATETIME_DATETIME_STORAGE_FORMAT);

$nids = \Drupal::entityQuery('node')
  ->condition('your_date_field.value', $formatted, '<=')
  ->range(0, 50)
  ->execute();

As of Drupal 8.5 change one should use following to get formatted date

use Drupal\Core\Datetime\DrupalDateTime;
use Drupal\datetime\Plugin\Field\FieldType\DateTimeItemInterface;

$date = new DrupalDateTime('21 days ago');
$date->setTimezone(new \DateTimezone(DateTimeItemInterface::STORAGE_TIMEZONE));
$formatted = $date->format(DateTimeItemInterface::DATETIME_STORAGE_FORMAT);