Format date in elasticsearch query (during retrieval)

elasticsearch date format
elasticsearch date query
elasticsearch multiple date formats
elasticsearch date format timezone
elasticsearch specify date format in query
elasticsearch change date format
elasticsearch number query
how to store date in elasticsearch

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.

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" ]
  }

Format date in elasticsearch query (during retrieval), I have a elasticsearch index with a field "aDate" (and lot of other fields) with the following mapping "aDate" : { "type" : "date", "format"  answer is here http://stackoverflow.com/questions/27931241/format-date-in-elasticsearch-query-during-retrieval/27932753. Am Dienstag, 13. Januar 2015 21:56:36 UTC+1

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.

Format date in elasticsearch query (during retrieval), When you run a query in Elasticsearch you can request it to return the raw data, for example specifying fields: curl -XGET  and I have a query to query from _source field which gives _reportDate field in string of 2015-12-05 01:05:00. I can't seems to find a way to get date in different date format during query retrieval apart from using script field (which is not preferable).

Since 5.0.0, es use Painless as script language: link

Try this (work in 6.3.2)

"script":"doc['aDate'].value.toString('yyyy-MM-dd HH:mm:ss')"

should datetime Elasticsearch's date for Elasticsearch query runner , I think Elasticsearch date is Re:dash datetime. Looking at the ES query runner, the date type is mapped to ES doesn't seem to differentiate them in the mappings, which is where this conversion information is retrieved from  However, the system this query is run on has only one host reporting to it, so in a production environment the number of hits will still be very high. To retrieve all CPU data for a specific host, a first naive attempt at an Elasticsearch query might be to add filters for the host.name and the metricset system.cpu: Sample query:

Scripting it only computes the answer when the row is extracted. This is expensive, and keeps you from using any date-related search functions in Elasticsearch.

You should create an elasticsearch "date" field before inserting it. Looks like a java Date() object will do.

Format date in elasticsearch query (during retrieval), Format date in elasticsearch query (during retrieval). I have a elasticsearch index with a field "aDate" (and lot of other fields) with the following  format (Optional, string) Date format used to convert date values in the query. By default, Elasticsearch uses the date format provided in the <field> 's mapping. This value overrides that mapping format.

Thanks @Archon for your suggestion. I used your answer as a guide to remove the time element from a datetime field in Elasticsearch

{
    "aggs": {
        "grp_by_date": {
            "terms": {
                "size": 200,
                "script": "doc['TransactionReconciliationsCreated'].value.toString('yyyy-MM-dd')"
            }
        }
    }
}

How to use a date or year/month with elasticsearch, Date-type field is shown as string. 2. Year / month (numeric) fields has random ordering 3. Month (label) field didn't test, but probably need  13 Format date in elasticsearch query (during retrieval) Jan 13 '15. 9 Is scala is faster that java since both runs on JVM? Oct 9 '15. 7 How Cassandra pagination

Elasticsearch Reference [1.7], Cluster Health; List All Indices; Create an Index; Index and Query a Document; Delete Date Format; Dynamic Mapping; Config Mappings; Meta; Transform Delaying allocation when a node leaves; Index recovery prioritization; Total Shards  Mapping is the outline of the documents stored in an index. It defines the data type like geo_point or string and format of the fields present in the documents and rules to control the mapping of dynamically added fields. Elasticsearch supports a number of different datatypes for the fields in a

Dynamic mappings and dates in ElasticSearch, type. Yet ElasticSearch can automatically map date fields for us. While this "just works" most of the time, it can be a good idea to help ElasticSearch help us by instead using naming Mappings for a type named tweet will be created for the index. Retrieving the mappings now gives us a different result. You can use the _source approach, but but in certain situations it can make sense to store a field. For instance, if you have a document with a title, a date, and a very large content field, you may want to retrieve just the title and the date without having to extract those fields from a large _source field: In this case, you'd use:

Elasticsearch DSL, Elasticsearch DSL is a high-level library whose aim is to help with writing and running defining mappings, retrieving and saving documents, wrapping the document data in from datetime import datetime from elasticsearch_dsl import Document, Date, Integer, insert complicated query here # Convert to Search object s  The new template for the metricbeat-* indices is already loaded into Elasticsearch, but it applies only to the newly created indices. Painless can be used to reindex the existing documents and apply the new template. The script below extracts the date from the index name and creates a new index with -1 appended.

Comments
  • thanks. since original date format is epoch i have to use the script. How can i get a result which includes also _source? (see bold marked text in the question)
  • I've updated the answer to include using _source to bring back other fields.
  • great, that work. If i use "_source" : "*" i get all source fields, thats what i wanted
  • use( groovy.time.TimeCategory ) don't seem to work with ES 2.2. Any idea, (lang is groovy)? No such property: groovy for class: 05c0662f39d36342a9a0a646db3aa789da16c191
  • not working in 6.x. Since 5.0.0, es use Painless as script language