How to sort by formatted date with Elastic Search?

Question

Suppose I have the following:

Name      Date
-----   -------------------
Alice   2013-02-10 15:17:00
Alice   2013-02-10 16:19:00
Alice   2013-02-10 17:21:00
Bob     2013-02-10 18:23:00
Alice   2013-02-10 19:25:00

This is what happens when I sort for date, then for name. It considers the hours first. What I really want is this:

Name      Date
-----   -------------------
Alice   2013-02-10 15:17:00
Alice   2013-02-10 16:19:00
Alice   2013-02-10 17:21:00
Alice   2013-02-10 19:25:00
Bob     2013-02-10 18:23:00

So, I want to sort first by %Y-%m-%d, then by name.

Is that possible?

Thanks

Bruno Saboia · Accepted Answer · 2014-09-04T13:24:18.333

1

It seems that you can use a sort _script for that:

"sort": {
"_script": {
  "script": "new java.text.SimpleDateFormat('yyyy-MM-dd').format(new Date(doc['date'].value))",
  "type": "string",
  "order": "desc"
},
"name": "asc"

Be aware that this is a security flaw. Read more at http://www.elasticsearch.org/guide/en/elasticsearch/reference/current/modules-scripting.html

edited Sep 04 '14 at 13:24

answered Aug 14 '14 at 19:38

Bruno Saboia

332
3
18

are you dates indexed as date types? – Dan Tuffery Aug 14 '14 at 19:41

score 0 · Answer 2 · answered May 27 '20 at 11:09

You might use ".keyword".

When we use below code, where added_on is date, what happens !! attribute text is analyzed, meaning it is broken up into distinct words when stored, and allows for free-text searches on one or more words in the field

so there is "text" and "keyword" associated with fields, so if we need to use aggregation in the query, we need the field value in general the keyword.

BEFORE

"_source":{....}
"query" : {...}
"sort": [
{
  "added_on": {
    "order": "desc"
  }
}
]

AFTER
"_source":{....}
"query" : {...}
"sort": [
{
  "added_on.keyword": {
    "order": "desc"
  }
}
]

How to sort by formatted date with Elastic Search?

2 Answers2