How to sort by date with ElasticSearch

Question

I have an index with a date field as following:

{
  "properties": {
    "productCreationDate": {
      "format": "YYYY-MM-dd'T'HH:mm:ssXXX",
      "type": "date"
    },
  }
}

When I perform a search that way:

{
  "size": 5,
  "from": 0,
  "sort": [
    {
      "productCreationDate": {
        "order": "desc"
      }
    }
  ],
  "track_scores": false
}

I get the documents in the inserting order an note the field order on ElasticSearch 7.9:

{
  "took": 24,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 6,
      "relation": "eq"
    },
    "max_score": null,
    "hits": [
      {
        "_index": "my-index",
        "_type": "_doc",
        "_id": "product^14",
        "_score": null,
        "_source": {
          "productCreationDate": "2020-08-14T18:21:51+02:00",
        },
        "sort": [
          1577722911000
        ]
      },
      {
        "_index": "my-index",
        "_type": "_doc",
        "_id": "product^28",
        "_score": null,
        "_source": {
          "productCreationDate": "2020-08-28T18:21:51+02:00",
        },
        "sort": [
          1577722911000
        ]
      },
      {
        "_index": "my-index",
        "_type": "_doc",
        "_id": "product^19",
        "_score": null,
        "_source": {
          "productCreationDate": "2020-08-19T18:21:51+02:00",
        },
        "sort": [
          1577722911000
        ]
      },
      {
        "_index": "my-index",
        "_type": "_doc",
        "_id": "product^27",
        "_score": null,
        "_source": {
          "productCreationDate": "2020-08-27T18:21:51+02:00",
        },
        "sort": [
          1577722911000
        ]
      },
      {
        "_index": "my-index",
        "_type": "_doc",
        "_id": "product^26",
        "_score": null,
        "_source": {
          "productCreationDate": "2020-08-26T18:21:51+02:00",
        },
        "sort": [
          1577722911000
        ]
      }
    ]
  }
}

What do I miss?

Edit: Thanks to @zaid warsi and @Yeikel I have changed the format to yyyy and I have a new order:

15
26
27
19
28
14

Which is even weirder since I asked for 5 documents.

Not sure if you meant to do that but YYYY means Week of Year instead of what you are probably intending to do (judging from your examples). For more details see stackoverflow.com/questions/15133549/…. — Yeikel
– Yeikel, Commented Aug 22, 2020 at 18:49

Zaid Warsi · Accepted Answer · 2020-08-22 18:27:35Z

5

YYYY is not a correct inbuilt year format in Elasticsearch.

Try changing your date format to yyyy-MM-dd'T'HH:mm:ssXXX, it should work. Refer this for valid inbuilt date formats, or you might need to define your own in the mapping.

answered Aug 22, 2020 at 18:27

Zaid Warsi

4213 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

GlinesMome Over a year ago

Sorry, it was a good hint but it is becomes weirder.

Zaid Warsi Over a year ago

Since your date is already ISO compliant, you can modify your mapping to: "properties": { "productCreationDate": { "type": "date" } } . Elasticsearch will automatically assign a proper format to it. The problem is that your date value and your date format doesn't match. I tested this on my side, the above mapping gave me proper sorted result.

LarsTech Over a year ago

Your link went stale.

Collectives™ on Stack Overflow

How to sort by date with ElasticSearch

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related