Saving date in microsecond format in ElasticSearch

Question

I am trying to save set of events from MySQL database into elastic search using jdbc input plugin with Logstash. The event record in database contains date fields which are in microseconds format. Practically, there are records in database between set of microseconds.

While importing data, Elasticsearch is truncating the microseconds date format into millisecond format. How could I save the data in microsecond format? The elasticsearch documentation says they follow the JODA time API to store date formats, which is not supporting the microseconds and truncating by adding a Z at the end of the timestamp.

Sample timestamp after truncation : 2018-05-02T08:13:29.268Z

Original timestamp in database : 2018-05-02T08:13:29.268482

Answer 1

The Z is not a result of the truncation but the GMT timezone.

ES supports microseconds, too, provided you've specified the correct date format in your mapping.

If the date field in your mapping is specified like this:

    "date": {
      "type": "date",
      "format": "yyyy-MM-dd'T'HH:mm:ss.SSSSSS"
    }

Then you can index your dates with the exact microsecond precision as you have in your database

UPDATE

Here is a full re-creation that shows you that it works:

PUT myindex
{
  "mappings": {
    "doc": {
      "properties": {
        "date": {
          "type": "date",
          "format": "yyyy-MM-dd'T'HH:mm:ss.SSSSSS"
        }
      }
    }
  }
}

PUT myindex/doc/1
{
  "date": "2018-05-02T08:13:29.268482"
}

Answer 2

Side note, "date" datatype stores data in milliseconds in elasticsearch so here in case nanoseconds precision level are wanted in date ranges queries; the appropriate datatype is date_nanos