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 https://stackoverflow.com/questions/15133549/difference-between-yyyy-and-yyyy-in-nsdateformatter#:~:text=A%20common%20mistake%20is%20to,however%20they%20may%20be%20different. — Yeikel, Aug 22 '20 at 18:49

score 5 · Accepted Answer · answered Aug 22 '20 at 18:27

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 '20 at 18:27

Zaid Warsi

421
3
12

Sorry, it was a good hint but it is becomes weirder. – GlinesMome Aug 23 '20 at 11:24
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. – Zaid Warsi Aug 23 '20 at 11:51
Your link went stale. – LarsTech Jan 25 '23 at 19:31

How to sort by date with ElasticSearch

1 Answers1