Profile picture for user admin
Daniel Sipos
27 Feb 2017

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->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');
  ->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).

Profile picture for user admin

Daniel Sipos

CEO @ Web Omelette

Danny founded WEBOMELETTE in 2012 as a passion project, mostly writing about Drupal problems he faced day to day, as well as about new technologies and things that he thought other developers would find useful. Now he now manages a team of developers and designers, delivering quality products that make businesses successful.

Contact us


Matt Fletcher 25 Sep 2017 13:26

Make sure to include DrupalDateTime

Thanks for this, super simple.

For anyone finding that DrupalDateTime is undefined, make sure to include it in your class:

use Drupal\Core\Datetime\DrupalDateTime;
Nick 03 Nov 2017 04:03

Thanks Danny!

Now I got all my datetimes storing correctly and I even think I figured out how to have my run-once-after-local-midnight-cron-hook-comparing-UTC-times to fire and work correctly!

Appreciated :)

Drifio 15 Aug 2018 15:04

Thanks for this!

Covers everything I needed mate, just wanted to say thanks!

Scott 16 Oct 2018 04:37

A DrupalDateTime challenge.

Hi Danny! Picture this: I have a user who sits in Dallas, Texas and manages apartment complexes from New York to Los Angeles. I can't use the user time zone, nor the "site" time zone to display content created in each of the separate zones. The user selects the apartment (which has a tzone indicator on the entity like America/Chicago) but when the views detail records display (and when displaying a node) how can the system display the proper time for the records in the zone? Scratching my head... Also, have benefitted from your posts in the past and really appreciate your insight.

Pavel 19 Apr 2019 17:23

Field Type Date Range

Hi Danny !.

There are two values for these fields: value and end_value

[field_date] => Array
                    [x-default] => Array
                            [0] => Array
                                    [value] => 2019-05-27T07:00:00
                                    [end_value] => 2019-05-28T14:00:00



Could you please ask how to write a query condition where end_value less than or equal current time for instance ?

Svetoslav 28 Jun 2019 08:38

field_name to use in entity query for date range

->condition('field_date.end_value', $start->format(DATETIME_DATETIME_STORAGE_FORMAT), '<=')
Steve 09 Jan 2020 02:11

Query field for today based on current user date

I have a custom date field (no time), and was wondering how I could query it for entities that match 'today'.

I tried setting;

$end->setTime(23, 59);

but that didn't work, and also tried

$today = new \DateTime('now', new \DateTimezone($timezone));
$today->setTimezone(new \DateTimeZone(DATETIME_STORAGE_TIMEZONE));
$today = DrupalDateTime::createFromDateTime($end);
->condition('field_cs_date', $today->format(DATETIME_DATETIME_STORAGE_FORMAT), '==');

Any help would be greatly appreciated

bahuma20 10 Jan 2020 13:46

Update this article to remove deprecated code


You now should use Drupal\datetime\Plugin\Field\FieldType\DateTimeItemInterface::STORAGE_TIMEZONE and Drupal\datetime\Plugin\Field\FieldType\DateTimeItemInterface::DATETIME_STORAGE_FORMAT

Could you please update this article because it is a good reference for this problem. This is number one search result in Google and it would be cool to show best practices here :)

Also a TLDR; at the start could help... (I didn't read to the part with the timezone and wondered why it wasnt working :D)

Anonymous 25 Mar 2020 03:54

The code has the typos:

The code has the typos:
Class DateTimeZone referenced with incorrect case: DateTimezone
Should be:
new \DateTimeZone()

johnzzon 03 Nov 2021 09:20

Thanks! (Also: deprecated code)

Big thanks for the article! Helped me filter out entities correctly in a query where I needed to get for this year, but an entity for 2022-01-01 was included.

Oh and btw, drupal_get_user_timezone() was deprecated in 8.8.x and removed in Drupal 9. We're supposed to use the built-in date_default_timezone_get() now.

Add new comment