Date aggregation - elasticsearch

Question

I am started working on elasticsearch recently, I am facing an issue in the date wise aggregation.

Mapping:

{
  "posts": {
    "mappings": {
        "facebook": {
            "properties": {
                "post_created_time": {
                    "type": "date"
                },
                "post_description": {
                    "type": "text"
                },
                "post_image": {
                    "type": "text"
                }
            }
        }
    }
  }
}

Source:

"hits": [
        {
            "_index": "posts",
            "_type": "facebook",
            "_id": "1027753751227740806",
            "_score": 1,
            "_source": {
                "post_created_time": 1436737816,
                "post_description": "captain's log: sailed a sea of booze after sundown with a new crew. bonds cemented. four more days of r&r before the next voyage. july the 5th, 2015 #captainslog #litely",
                "post_image": "https://scontent.cdninstagram.com/t51.2885-15/s640x640/sh0.08/e35/11419297_845398318885282_1471458983_n.jpg?ig_cache_key=MTAyNzc1Mzc1MTIyNzc0MDgwNg%3D%3D.2"
            }
        },
        {
            "_index": "posts",
            "_type": "facebook",
            "_id": "1030858912926085173",
            "_score": 1,
            "_source": {
                "post_created_time": 1436218427,
                "post_description": "wanna go for a ride? #?",
                "post_image": "https://scontent.cdninstagram.com/t51.2885-15/s640x640/sh0.08/e35/1739201_510171422480872_883567488_n.jpg?ig_cache_key=MTAzMDg1ODkxMjkyNjA4NTE3Mw%3D%3D.2"
            }
        }
]

Query I have tried:

{  
 "size":0,
 "aggs":{  
     "metrics_by_day":{  
     "date_histogram":{  
        "field":"post_created_time",
        "interval":"day"
     }
   }
 }
}

Output for the above query:

"aggregations": {
    "metrics_by_day": {
        "buckets": [
            {
                "key_as_string": "1970-01-17",
                "key": 1382400000,
                "doc_count": 2
            }
        ]
    }
}

Expected output is:

"aggregations": {
    "metrics_by_day": {
        "buckets": [
            {
                "key_as_string": "2015-07-12",
                "doc_count": 1
            },
            {
                "key_as_string": "2015-07-06",
                "doc_count": 1
            }
        ]
    }
}

Any suggestion will be grateful

Answer 1

There were problems with the date format in your mapping and your aggregation. The format of your date type is "format" : "epoch_second" (https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html) and you have to specify a date format of your DateHistogramm Aggregation "format" : "yyyy-MM-dd" (https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html)

Here are the corrected mapping:

{
"posts": {
    "mappings": {
        "facebook": {
            "properties": {
                "post_created_time": {
                    "type": "date",
                     "format":"epoch_second"
                },
                "post_description": {
                    "type": "text"
                },
                "post_image": {
                    "type": "text"
                }
            }
        }
    }
  }
}

And your aggregation:

{  
 "size":0,
 "aggs":{  
     "metrics_by_day":{  
     "date_histogram":{  
        "field":"post_created_time",
        "interval":"day",
        "format" : "yyyy-MM-dd"
     }
   }
 }
}