Date Handling in Elasticsearch

Sep 18, 2019

Introduction

Elasticsearch supports the date type as a type for storing and querying using dates and times. Dates can be stored and used for querying in the following flavors.

  • an ISO date such as 2008-04-21, without a time component.

  • an ISO date-time such as 2008-04-21T10:32:45 which includes a time component.

When data is imported into Elasticsearch from java, you can send a LocalDate object or a LocalDateTime object, depending on whether your data includes a time component or is just date only.

Listing a Mapping

You can view the mapping of an index to verify whether a particular field is stored as a date. To do that, use the following request.

curl -X GET "localhost:9200/orders.csv/_mapping"

Which results in the following output. Note that Order Date is stored as a date.

"orders.csv": {
    "mappings": {
        "doc": {
            "properties": {
                "Customer ID": {
                    "type": "long"
                },
                "Employee ID": {
                    "type": "long"
                },
                "Notes": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "Order Date": {
                    "type": "date"
                },
...

Querying by date

A sample set of hits from the index listed above is shown below. As you can see, the field Order Date contains a date-time in ISO format. (For this example, the time is 00:00:00; that is how the original data was presented.)

"hits": [
  {
    "_index": "orders.csv",
    "_type": "doc",
    "_id": "1",
    "_score": 1.0,
    "_source": {
      "Order ID": 30,
      "Employee ID": 9,
      "Customer ID": 27,
      "Order Date": "2006-01-15T00:00:00.000",
      "Shipped Date": "2006-01-22T00:00:00.000",
      "Shipper ID": 2,
      "Ship Name": "Karen Toh",
      "Ship Address": "789 27th Street",
      "Ship City": "Las Vegas",
      "Ship State/Province": "NV",
      "Ship ZIP/Postal Code": 99999,
      "Ship Country/Region": "USA",
      "Shipping Fee": "$200.00",
      "Taxes": "$0.00",
      "Payment Type": "Check",
      "Paid Date": "2006-01-15T00:00:00.000",
      "Notes": "",
      "Tax Rate": 0.0,
      "Tax Status": "",
      "Status ID": 3
    }
  },
...

The following query is used for fetching hits that restrict the date to on or before 2006-01-15 (using the lte operator). Note that we specify just the date in the form 1/16/2006 and a suitable format to go with it.

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "Order Date": {
              "format": "M/d/yyyy",
              "lte": "1/15/2006"
            }
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 25
}

The results are shown partially below.

"hits": [
  {
    "_index": "joe.csv",
    "_type": "doc",
    "_id": "1",
    "_score": 1.0,
    "_source": {
      "Order ID": 30,
      "Employee ID": 9,
      "Customer ID": 27,
      "Order Date": "2006-01-15T00:00:00.000",
      "Shipped Date": "2006-01-22T00:00:00.000",
      "Shipper ID": 2,
      "Ship Name": "Karen Toh",
      "Ship Address": "789 27th Street",
      "Ship City": "Las Vegas",
      "Ship State/Province": "NV",
      "Ship ZIP/Postal Code": 99999,
      "Ship Country/Region": "USA",
      "Shipping Fee": "$200.00",
      "Taxes": "$0.00",
      "Payment Type": "Check",
      "Paid Date": "2006-01-15T00:00:00.000",
      "Notes": "",
      "Tax Rate": 0.0,
      "Tax Status": "",
      "Status ID": 3
    }
  }
...

Date Format directives

You can specify the date format by setting the format property to a combination of the codes shown below.

Code Meaning Examples
G era AD; Anno Domini; A
u year 2004; 04
y year-of-era 2004; 04
D day-of-year 189
M month-of-year 7; 07; Jul; July; J
L month-of-year 7; 07; Jul; July; J
d day-of-month 10
Q/q quarter-of-year 3; 03; Q3; 3rd quarter
Y week-based-year 1996; 96
w week-of-week-based-year 27
W week-of-month 4
E day-of-week Tue; Tuesday; T
e/c localized day-of-week 2; 02; Tue; Tuesday; T
F week-of-month 3
a am-pm-of-day PM
h clock-hour-of-am-pm (1-12) 12
K hour-of-am-pm (0-11) 0
k clock-hour-of-am-pm (1-24) 0
H hour-of-day (0-23) 0
m minute-of-hour 30
s second-of-minute 55
S fraction-of-second 978
A milli-of-day 1234
n nano-of-second 987654321
N nano-of-day 1234000000
V time-zone ID America/Los_Angeles; Z; -08:30
z time-zone name Pacific Standard Time; PST
O localized zone-offset GMT+8; GMT+08:00; UTC-08:00;
X zone-offset ‘Z’ for zero Z; -08; -0830; -08:30; -083015; -08:30:15;
x zone-offset +0000; -08; -0830; -08:30; -083015; -08:30:15;
Z zone-offset +0000; -0800; -08:00;
p pad next 1
’ escape for text ‘arbitrary text here’
’’ single quote ‘we have here a’’ (single quote)’
[ optional section start
] optional section end

Importing data containing dates

Argon automatically identifies data types when importing data in most cases. This is done by parsing the first 100 rows to identify the data types as one of: integer, float, boolean and date.

In the image below, two fields birth_date and hire_date have been identified as dates without a time component.

(click for larger image)

The following shows importing a file with dates including a time component.

(click for larger image)

Querying date fields in Argon

1. Invoke the Filter-by-Date dialog by choosing Select Date Range from the context menu.

(click for larger image)

2. The Filter-by-Date dialog is shown.

Select the date to filter by, and the condition from among <, <=, > or >=. Since the date field does not include time, the time field is grayed out. Click Apply to run the query.

(click for larger image)

3. The query JSON is shown below.

The selected date and the format are specified as shown.

{
  "from": 0,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "birth_date": {
              "lt": "1960-1-11",
              "format": "yyyy-M-d"
            }
          }
        }
      ]
    }
  },
  "size": 25
}

And a snapshot of the results:

"hits": [
  {
    "_index": "employees.csv",
    "_type": "doc",
    "_id": "70001",
    "_score": 1.0,
    "_source": {
      "emp_no": 80001,
      "birth_date": "1956-09-30",
      "first_name": "Nagui",
      "last_name": "Merli",
      "gender": null,
      "hire_date": "1986-06-12"
    }
  },
  {
    "_index": "employees.csv",
    "_type": "doc",
    "_id": "70005",
    "_score": 1.0,
    "_source": {
      "emp_no": 80005,
      "birth_date": "1957-04-01",
      "first_name": "Sanjit",
      "last_name": "Nyrup",
      "gender": null,
      "hire_date": "1989-02-12"
    }
  },
...

Searching for a specific date

To run a query restricting a field to a particular date, specify both <= and >= with the same date.

(click for larger image)

The query JSON is shown below.

{
  "size": 25,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "birth_date": {
              "lte": "1962-9-5",
              "gte": "1962-9-5",
              "format": "yyyy-M-d"
            }
          }
        }
      ]
    }
  },
  "from": 0
}

And the result hits:

"hits": [
  {
    "_index": "employees.csv",
    "_type": "doc",
    "_id": "70010",
    "_score": 1.0,
    "_source": {
      "emp_no": 80010,
      "birth_date": "1962-09-05",
      "first_name": "Olivera",
      "last_name": "Munke",
      "gender": null,
      "hire_date": "1990-03-24"
    }
  },
  {
    "_index": "employees.csv",
    "_type": "doc",
    "_id": "73885",
    "_score": 1.0,
    "_source": {
      "emp_no": 83885,
      "birth_date": "1962-09-05",
      "first_name": "Raimond",
      "last_name": "Merks",
      "gender": false,
      "hire_date": "1989-08-13"
    }
  },
...

Search for a specific date and time

With a DateTime field, you can query for a specific date and time. When the field supports time, the time field will be active and you can enter the time.

(click for larger image)

The query is shown below. Note that the specified time is included, along with the format the date is specified in.

{
  "size": 25,
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "Measurement Timestamp": {
              "lte": "5/28/2014 1:0:0 PM",
              "format": "M/d/yyyy' 'h:m:s a"
            }
          }
        }
      ]
    }
  },
  "from": 0
}

And you can see the results below.

(click for larger image)

Date and time range

Here is an example which shows a date-time range query.

(click for larger image)

The query JSON is shown below. Date and time are included in the query along with a suitable format.

{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "Measurement Timestamp": {
              "format": "M/d/yyyy' 'h:m:s a",
              "lte": "5/28/2014 2:0:0 PM",
              "gte": "5/28/2014 1:0:0 PM"
            }
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 25
}

A sampling of the hits:

"hits": [
  {
    "_index": "beach_water_quality_-_automated_sensors.csv",
    "_type": "doc",
    "_id": "6",
    "_score": 1.0,
    "_source": {
      "Beach Name": "Montrose Beach",
      "Measurement Timestamp": "2014-05-28T13:00:00.000",
      "Water Temperature": 14.5,
      "Turbidity": 2.72,
      "Transducer Depth": 1.395,
      "Wave Height": 0.306,
      "Wave Period": 3,
      "Battery Life": 11.9,
      "Measurement Timestamp Label": "2014-05-28T13:00:00.000",
      "Measurement ID": "MontroseBeach201405281300"
    }
  },
  {
    "_index": "beach_water_quality_-_automated_sensors.csv",
    "_type": "doc",
    "_id": "7",
    "_score": 1.0,
    "_source": {
      "Beach Name": "Calumet Beach",
      "Measurement Timestamp": "2014-05-28T13:00:00.000",
      "Water Temperature": 16.3,
      "Turbidity": 1.28,
      "Transducer Depth": 1.524,
      "Wave Height": 0.162,
      "Wave Period": 4,
      "Battery Life": 11.7,
      "Measurement Timestamp Label": "2014-05-28T13:00:00.000",
      "Measurement ID": "CalumetBeach201405281300"
    }
  },

Summary

While Elasticsearch supports storage and querying date and time, it is easiest to use an application like Argon to help with the data import. Also, composing dates and times in a suitable format is all handled in a intuitive way for you.