Date field type

Introduced 1.0

A date in OpenSearch can be represented as one of the following:

• A long value that corresponds to milliseconds since the epoch (the value must be non-negative). Dates are stored in this form internally.
• A formatted string.
• An integer value that corresponds to seconds since the epoch (the value must be non-negative).

To represent date ranges, there is a date range field type.

Example

Create a mapping with a date field and two date formats:

PUT testindex
{
  "mappings" : {
    "properties" :  {
      "release_date" : {
        "type" : "date",
        "format" : "strict_date_optional_time||epoch_millis"
      }
    }
  }
}

copy

Parameters

The following table lists the parameters accepted by date field types. All parameters are optional.

Formats

OpenSearch has built-in date formats, but you can also create your own custom formats. You can specify multiple date formats, separated by ||.

Default format

As of OpenSearch 2.12, you can to choose to use an experimental default date format, strict_date_time_no_millis||strict_date_optional_time||epoch_millis. To use the experimental default, set the opensearch.experimental.optimization.datetime_formatter_caching.enabled feature flag to true. For more information about enabling and disabling feature flags, see Enabling experimental features.

Built-in formats

Most of the date formats have a strict_ counterpart. When the format starts with strict_, the date must have the correct number of digits specified in the format. For example, if the format is set to strict_year_month_day ("yyyy-MM-dd"), both month and day have to be two-digit numbers. So, "2020-06-09" is valid, while "2020-6-9" is invalid.

Epoch is defined as 00:00:00 UTC on January 1, 1970.

y: year
Y: week-based year
M: month
w: ordinal week of the year from 01 to 53
d: day
D: ordinal day of the year from 001 to 365 (366 for leap years)
e: ordinal day of the week from 1 (Monday) to 7 (Sunday)
H: hour from 0 to 23
m: minute
s: second
S: fraction of a second
Z: time zone offset (for example, +0400; -0400; -04:00)

Numeric date formats

Basic date formats

Components of basic date formats are not separated by a delimiter. For example, “20190323”.

Full date formats

Components of full date formats are separated by a - delimiter for date and : delimiter for time. For example, "2019-03-23T21:34".

Custom formats

You can create custom formats for date fields. For example, the following request specifies a date in the common "MM/dd/yyyy" format:

PUT testindex
{
  "mappings" : {
    "properties" :  {
      "release_date" : {
        "type" : "date",
        "format" : "MM/dd/yyyy"
      }
    }
  }
}

copy

Index a document with a date:

PUT testindex/_doc/21 
{
  "release_date" : "03/21/2019"
}

copy

When searching for an exact date, provide that date in the same format:

GET testindex/_search
{
  "query" : {
    "match": {
      "release_date" : {
        "query": "03/21/2019"
      }
    }
  }
}

copy

Range queries by default use the field’s mapped format. You can also specify the range of dates in a different format by providing the format parameter:

GET testindex/_search
{
  "query": {
    "range": {
      "release_date": {
        "gte": "2019-01-01",
        "lte": "2019-12-31",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

copy

Date math

The date field type supports using date math to specify durations in queries. For example, the gt, gte, lt, and lte parameters in range queries and the from and to parameters in date range aggregations accept date math expressions.

A date math expression contains a fixed date, optionally followed by one or more mathematical expressions. The fixed date may be either now (current date and time in milliseconds since the epoch) or a string ending with || that specifies a date (for example, 2022-05-18||). The date must be in the default format (which is strict_date_time_no_millis||strict_date_optional_time||epoch_millis by default).

If you specify multiple date formats in the field mapping, OpenSearch uses the first format to convert the milliseconds since the epoch value to a string.
If a field mapping for a field contains no format, OpenSearch uses the strict_date_optional_time format to convert the epoch value to a string.

Date math supports the following mathematical operators.

Date math supports the following time units:

y: Years
M: Months
w: Weeks
d: Days
h or H: Hours
m: Minutes
s: Seconds

Example expressions

The following example expressions illustrate using date math:

• now+1M: The current date and time in milliseconds since the epoch, plus 1 month.
• 2022-05-18||/M: 05/18/2022, rounded to the beginning of the month. Resolves to 2022-05-01.
• 2022-05-18T15:23||/h: 15:23 on 05/18/2022, rounded to the beginning of the hour. Resolves to 2022-05-18T15.
• 2022-05-18T15:23:17.789||+2M-1d/d: 15:23:17.789 on 05/18/2022 plus 2 months minus 1 day, rounded to the beginning of the day. Resolves to 2022-07-17.

Using date math in a range query

The following example illustrates using date math in a range query.

Set up an index with release_date mapped as date:

PUT testindex 
{
  "mappings" : {
    "properties" :  {
      "release_date" : {
        "type" : "date"
      }
    }
  }
}

copy

Index two documents into the index:

PUT testindex/_doc/1
{
  "release_date": "2022-09-14"
}

copy

PUT testindex/_doc/2
{
  "release_date": "2022-11-15"
}

copy

The following query searches for documents with release_date within 2 months and 1 day of 09/14/2022. The lower boundary of the range is rounded to the beginning of the day on 09/14/2022:

GET testindex/_search
{
  "query": {
    "range": {
      "release_date": {
        "gte": "2022-09-14T15:23||/d",
        "lte": "2022-09-14||+2M+1d"
      }
    }
  }
}

copy

The response contains both documents:

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "testindex",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "release_date" : "2022-11-14"
        }
      },
      {
        "_index" : "testindex",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "release_date" : "2022-09-14"
        }
      }
    ]
  }
}