Format date in elasticsearch query (during retrieval)

Question

I have a elasticsearch index with a field "aDate" (and lot of other fields) with the following mapping

"aDate" : {
        "type" : "date",
        "format" : "date_optional_time"
}

When i query for a document i get a result like

"aDate" : 1421179734000,

I know this is the epoch, the internal java/elasticsearch date format, but i want to have a result like:

"aDate" : "2015-01-13T20:08:54",

I play around with scripting

{  
 "query":{  
   "match_all":{  

   }
 },
 "script_fields":{  
   "aDate":{  
      "script":"if (!_source.aDate?.equals('null')) new java.text.SimpleDateFormat('yyyy-MM-dd\\'T\\'HH:mm:ss').format(new java.util.Date(_source.aDate));"
   }
 }
}

but it give strange results (script works basically, but aDate is the only field returned and _source is missing). This looks like

"hits": [{
        "_index": "idx1",
        "_type": "type2",
        "_id": "8770",
        "_score": 1.0,
        "fields": {
            "aDate": ["2015-01-12T17:15:47"]
        }
    },

I would prefer a solution without scripting if possible.

Answer 1

When you run a query in Elasticsearch you can request it to return the raw data, for example specifying fields:

curl -XGET http://localhost:9200/myindex/date-test/_search?pretty -d '
{
 "fields" : "aDate",
 "query":{  
   "match_all":{  

   }
 }
}'

Will give you the date in the format that you originally stored it:

{
  "_index" : "myindex",
  "_type" : "date-test",
  "_id" : "AUrlWNTAk1DYhbTcL2xO",
  "_score" : 1.0,
  "fields" : {
    "aDate" : [ "2015-01-13T20:08:56" ]
  }
}, {
  "_index" : "myindex",
  "_type" : "date-test",
  "_id" : "AUrlQnFgk1DYhbTcL2xM",
  "_score" : 1.0,
  "fields" : {
    "aDate" : [ 1421179734000 ]
  }

It's not possible to change the date format unless you use a script.

curl -XGET http://localhost:9200/myindex/date-test/_search?pretty -d '
{  
 "query":{  
   "match_all":{ }
 },
 "script_fields":{  
   "aDate":{  
      "script":"use( groovy.time.TimeCategory ) { new Date( doc[\"aDate\"].value )  }"
   }
 }
}'

Will return:

{
  "_index" : "myindex",
  "_type" : "date-test",
  "_id" : "AUrlWNTAk1DYhbTcL2xO",
  "_score" : 1.0,
  "fields" : {
    "aDate" : [ "2015-01-13T20:08:56.000Z" ]
  }
}, {
  "_index" : "myindex",
  "_type" : "date-test",
  "_id" : "AUrlQnFgk1DYhbTcL2xM",
  "_score" : 1.0,
  "fields" : {
    "aDate" : [ "2015-01-13T20:08:54.000Z" ]
  }
}

To apply a format, append it as follows:

"script":"use( groovy.time.TimeCategory ){ new Date( doc[\"aDate\"].value ).format(\"yyyy-MM-dd\")   }"

will return "aDate" : [ "2015-01-13" ]

To display the T, you'll need to use quotes but replace them with the Unicode equivalent:

"script":"use( groovy.time.TimeCategory ){ new Date( doc[\"aDate\"].value ).format(\"yyyy-MM-dd\u0027T\u0027HH:mm:ss\") }"

returns "aDate" : [ "2015-01-13T20:08:54" ]

---

To return script_fields and source

Use _source in your query to specify the fields you want to return:

curl -XGET http://localhost:9200/myindex/date-test/_search?pretty -d '
 {  "_source" : "name",
  "query":{
    "match_all":{ }
  },
  "script_fields":{
    "aDate":{
       "script":"use( groovy.time.TimeCategory ) { new Date( doc[\"aDate\"].value )  }"
    }
  }
 }'

Will return my name field:

"_source":{"name":"Terry"},
  "fields" : {
    "aDate" : [ "2015-01-13T20:08:56.000Z" ]
  }

Using asterisk will return all fields, e.g.: "_source" : "*",

"_source":{"name":"Terry","aDate":1421179736000},
  "fields" : {
    "aDate" : [ "2015-01-13T20:08:56.000Z" ]
  }

Answer 2

As LabOctoCat mentioned, Olly Cruickshank answer no longer works in elastic 2.2. I changed the script to:

"script":"new Date(doc['time'].value)"

You can format the date according to this.