10

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.

4
  • Doesn't >= work? 20160318 >= 20160218
    – apaderno
    Apr 18 '16 at 3:43
  • The data is stored as XXXX-XX-XX (2016-04-18), so it's technically not a number, therefore doing a numerical condition against it wouldn't be valid.
    – Decipher
    Apr 18 '16 at 5:19
  • 1
    '2016-04-18' > '2016-03-18' works for me, and '2014-04-18' > '2018-02-01' returns FALSE. Yes, it's a string comparison, but given the string format, it should work. After all, '1' < '2'.
    – apaderno
    Apr 18 '16 at 5:40
  • 1
    The problem would be with '0001' < '2', maybe.
    – apaderno
    Apr 18 '16 at 5:43
23

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);
2
  • This worked great for a field with timestamp—in the database the storage was like 2016-07-18T13:00:00, but Drupal handled the conversion and comparison properly using this setup. Sep 29 '16 at 21:14
  • This didn't work for me using the created field, but just passing in the timestamp returned by $node->getCreatedTime() did (Drupal 8.8.x).
    – Tim
    Dec 17 '20 at 7:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.