Date Formatting and Date Math

Date Formatting

Solr’s date fields (DatePointField, DateRangeField and the deprecated TrieDateField) represent "dates" as a point in time with millisecond precision. The format used is a restricted form of the canonical representation of dateTime in the XML Schema specification – a restricted subset of ISO-8601. For those familiar with Java date handling, Solr uses DateTimeFormatter.ISO_INSTANT for formatting, and parsing too with "leniency".

YYYY-MM-DDThh:mm:ssZ

  • YYYY is the year.

  • MM is the month.

  • DD is the day of the month.

  • hh is the hour of the day as on a 24-hour clock.

  • mm is minutes.

  • ss is seconds.

  • Z is a literal 'Z' character indicating that this string representation of the date is in UTC

Note that no time zone can be specified; the String representations of dates is always expressed in Coordinated Universal Time (UTC). Here is an example value:

1972-05-20T17:33:18Z

You can optionally include fractional seconds if you wish, although any precision beyond milliseconds will be ignored. Here are example values with sub-seconds:

  • 1972-05-20T17:33:18.772Z

  • 1972-05-20T17:33:18.77Z

  • 1972-05-20T17:33:18.7Z

There must be a leading '-' for dates prior to year 0000, and Solr will format dates with a leading '+' for years after 9999. Year 0000 is considered year 1 BC; there is no such thing as year 0 AD or BC.

Query escaping may be required

As you can see, the date format includes colon characters separating the hours, minutes, and seconds. Because the colon is a special character to Solr’s most common query parsers, escaping is sometimes required, depending on exactly what you are trying to do.

This is normally an invalid query: datefield:1972-05-20T17:33:18.772Z

These are valid queries:
datefield:1972-05-20T17\:33\:18.772Z
datefield:"1972-05-20T17:33:18.772Z"
datefield:[1972-05-20T17:33:18.772Z TO *]

Date Range Formatting

Solr’s DateRangeField supports the same point in time date syntax described above (with date math described below) and more to express date ranges. One class of examples is truncated dates, which represent the entire date span to the precision indicated. The other class uses the range syntax ([ TO ]). Here are some examples:

  • 2000-11 – The entire month of November, 2000.

  • 1605-11-05 – The Fifth of November.

  • 2000-11-05T13 – Likewise but for an hour of the day (1300 to before 1400, i.e., 1pm to 2pm).

  • -0009 – The year 10 BC. A 0 in the year position is 0 AD, and is also considered 1 BC.

  • [2000-11-01 TO 2014-12-01] – The specified date range at a day resolution.

  • [2014 TO 2014-12-01] – From the start of 2014 till the end of the first day of December.

  • [* TO 2014-12-01] – From the earliest representable time thru till the end of the day on 2014-12-01.

Limitations: The range syntax doesn’t support embedded date math. If you specify a date instance supported by DatePointField with date math truncating it, like NOW/DAY, you still get the first millisecond of that day, not the entire day’s range. Exclusive ranges (using { & }) work in queries but not for indexing ranges.

Date Math

Solr’s date field types also supports date math expressions, which makes it easy to create times relative to fixed moments in time, include the current time which can be represented using the special value of “NOW”.

Date Math Syntax

Date math expressions consist either adding some quantity of time in a specified unit, or rounding the current time by a specified unit. Expressions can be chained and are evaluated left to right.

For example: this represents a point in time two months from now:

NOW+2MONTHS

This is one day ago:

NOW-1DAY

A slash is used to indicate rounding. This represents the beginning of the current hour:

NOW/HOUR

The following example computes (with millisecond precision) the point in time six months and three days into the future and then rounds that time to the beginning of that day:

NOW+6MONTHS+3DAYS/DAY

Note that while date math is most commonly used relative to NOW it can be applied to any fixed moment in time as well:

1972-05-20T17:33:18.772Z+6MONTHS+3DAYS/DAY

Date Math Unit Options

The following units are valid for use in date math expressions. The first column is the value used in date math expressions in Solr. The second column is the chronological unit to which it maps, as multiple aliases exist for given units of time.

Date Math Expression Unit Chronological Unit

YEAR

Years

YEARS

Years

MONTH

Months

MONTHS

Months

DAY

Days

DAYS

Days

DATE

Days

HOUR

Hours

HOURS

Hours

MINUTE

Minutes

MINUTES

Minutes

SECOND

Seconds

SECONDS

Seconds

MILLI

Milliseconds

MILLIS

Milliseconds

MILLISECOND

Milliseconds

MILLISECONDS

Milliseconds

Request Parameters That Affect Date Math

NOW

The NOW parameter is used internally by Solr to ensure consistent date math expression parsing across multiple nodes in a distributed request. But it can be specified to instruct Solr to use an arbitrary moment in time (past or future) to override for all situations where the special value of NOW would impact date math expressions.

It must be specified as a (long valued) milliseconds since epoch.

Example:

q=solr&fq=start_date:[* TO NOW]&NOW=1384387200000

TZ

By default, all date math expressions are evaluated relative to the UTC TimeZone, but the TZ parameter can be specified to override this behaviour, by forcing all date based addition and rounding to be relative to the specified time zone.

For example, the following request will use range faceting to facet over the current month, "per day" relative UTC:

http://localhost:8983/solr/my_collection/select?q=*:*&facet.range=my_date_field&facet=true&facet.range.start=NOW/MONTH&facet.range.end=NOW/MONTH%2B1MONTH&facet.range.gap=%2B1DAY&wt=xml
<int name="2013-11-01T00:00:00Z">0</int>
<int name="2013-11-02T00:00:00Z">0</int>
<int name="2013-11-03T00:00:00Z">0</int>
<int name="2013-11-04T00:00:00Z">0</int>
<int name="2013-11-05T00:00:00Z">0</int>
<int name="2013-11-06T00:00:00Z">0</int>
<int name="2013-11-07T00:00:00Z">0</int>
...

While in this example, the "days" will be computed relative to the specified time zone - including any applicable Daylight Savings Time adjustments:

http://localhost:8983/solr/my_collection/select?q=*:*&facet.range=my_date_field&facet=true&facet.range.start=NOW/MONTH&facet.range.end=NOW/MONTH%2B1MONTH&facet.range.gap=%2B1DAY&TZ=America/Los_Angeles&wt=xml
<int name="2013-11-01T07:00:00Z">0</int>
<int name="2013-11-02T07:00:00Z">0</int>
<int name="2013-11-03T07:00:00Z">0</int>
<int name="2013-11-04T08:00:00Z">0</int>
<int name="2013-11-05T08:00:00Z">0</int>
<int name="2013-11-06T08:00:00Z">0</int>
<int name="2013-11-07T08:00:00Z">0</int>
...

More DateRangeField Details

DateRangeField is almost a drop-in replacement for places where DatePointField is used. The only difference is that Solr’s XML or SolrJ response formats will expose the stored data as a String instead of a Date. The underlying index data for this field will be a bit larger. Queries that align to units of time a second on up should be faster than TrieDateField, especially if it’s in UTC.

The main point of DateRangeField, as its name suggests, is to allow indexing date ranges. To do that, simply supply strings in the format shown above. It also supports specifying 3 different relational predicates between the indexed data, and the query range:

  • Intersects (default)

  • Contains

  • Within

You can specify the predicate by querying using the op local-params parameter like so:

fq={!field f=dateRange op=Contains}[2013 TO 2018]

Unlike most local params, op is actually not defined by any query parser (field), it is defined by the field type, in this case DateRangeField. In the above example, it would find documents with indexed ranges that contain (or equals) the range 2013 thru 2018. Multi-valued overlapping indexed ranges in a document are effectively coalesced.

An Example Use Case

Suppose we want to find all restaurants that are open within a certain time window. Let’s add a date range field to the schema.xml, so that we could index the information about the restaurant opening hours:

<field name="opening_hours" type="date_range" indexed="true" stored="true" multiValued="true"/>
<fieldType name="date_range" class="solr.DateRangeField"/>

Next, we will add two restaurants to the index:

JSON

[{ "id": "r01",
   "opening_hours": [ "[2016-02-01T03:00Z TO 2016-02-01T15:00Z]",
                      "[2016-02-02T03:00Z TO 2016-02-02T15:00Z]",
                      "[2016-02-03T03:00Z TO 2016-02-03T15:00Z]",
                      "[2016-02-04T03:00Z TO 2016-02-04T15:00Z]",
                      "[2016-02-05T03:00Z TO 2016-02-05T16:00Z]",
                      "[2016-02-06T03:00Z TO 2016-02-06T16:00Z]",
                      "[2016-02-07T03:00Z TO 2016-02-07T15:00Z]" ]},
 { "id": "r02",
   "opening_hours": [ "[2016-02-06T10:00Z TO 2016-02-06T12:00Z]",
                      "[2016-02-06T14:00Z TO 2016-02-06T16:00Z]",
                      "[2016-02-07T12:00Z TO 2016-02-07T16:00Z]" ]}
]

Each restaurant can have multiple opening hours in a single day, and the opening hours can be different on different days.

The date ranges in opening_hours should be converted to UTC before indexing.

Now, to find the restaurants that are open during a specific time window, we can use a filter query:

fq={!field f=opening_hours op=Contains}[2016-02-02T14:50 TO 2016-02-02T15:00]
{
  "responseHeader":{
    "status":0,
    "QTime":29,
    "params":{
      "q":"id:*",
      "fl":"id",
      "fq":"{!field f=opening_hours op=Contains}[2016-02-02T14:50 TO 2016-02-02T15:00]",
      "wt":"json"}},
  "response":{"numFound":1,"start":0,"numFoundExact":true,"docs":[
      {
        "id":"r01"}]
  }}

And if we need to get opening hour ranges, we can use a facet query:

q=id:*
rows=0
facet=true
facet.range=opening_hours
f.opening_hours.facet.range.start=NOW
f.opening_hours.facet.range.end=NOW+6HOUR
f.opening_hours.facet.range.gap=+1HOUR
{
  "responseHeader":{
    "status":0,
    "QTime":16,
    "params":{
      "q":"id:*",
      "facet":"true",
      "facet.range":"opening_hours",
      "f.opening_hours.facet.range.start":"NOW",
      "f.opening_hours.facet.range.gap":"+1HOUR",
      "f.opening_hours.facet.range.end":"NOW+6HOUR",
      "rows":"0",
      "wt":"json"}},
  "response":{"numFound":2,"start":0,"numFoundExact":true,"docs":[]
  },
  "facet_counts":{
    "facet_queries":{},
    "facet_fields":{},
    "facet_ranges":{
      "opening_hours":{
        "counts":[
          "2016-02-06T11:01:00Z",2,
          "2016-02-06T12:01:00Z",1,
          "2016-02-06T13:01:00Z",2,
          "2016-02-06T14:01:00Z",2,
          "2016-02-06T15:01:00Z",2,
          "2016-02-06T16:01:00Z",0],
        "gap":"+1HOUR",
        "start":"2016-02-06T11:01:00Z",
        "end":"2016-02-06T17:01:00Z"}},
    "facet_intervals":{},
    "facet_heatmaps":{}}}

The query results show how many restaurants will be open in the next 6 hours, with a breakdown for each of the six consecutive one-hour intervals.