# Sorting es results based on a Json field value of type date

**URL:** <https://discuss.elastic.co/t/sorting-es-results-based-on-a-json-field-value-of-type-date/48950>\
**Category:** Elasticsearch\
**Created:** [May 2, 2016, 12:46pm UTC](https://discuss.elastic.co/t/sorting-es-results-based-on-a-json-field-value-of-type-date/48950 "2016-05-02T12:46:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mohigupt](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mohigupt](https://discuss.elastic.co/u/mohigupt)\
**Post date:** [May 2, 2016, 12:46pm UTC](https://discuss.elastic.co/t/sorting-es-results-based-on-a-json-field-value-of-type-date/48950/1 "2016-05-02T12:46:21Z")

</div>

Hi,  
i have recently started using elasticsearch and am completely lost on how to sort the documents based on a field value for which I have defined the type as "date" and a custom format as "yyyy-MM-dd HH:mm:ss -HHmm". I have define the index mapping at time of index creation as :  
curl -XPOST localhost:9200/myindex -d '{  
"mappings" : {  
"type1" : {  
"properties": {  
"logtime": {  
"type": "date",  
"format":"yyyy-MM-dd HH:mm:ss -HHmm",  
"index" : "not\_analyzed"  
},  
"hostname": {  
"type": "string"  
},  
"level": {  
"type": "string"  
},  
"log": {  
"type": "string"  
},  
"user": {  
"type": "string"  
}  
}  
}  
}  
}'  
I inserted few documenst as :  
{"hostname" : "node1", "level" : "info", "log" : "this is dummy log1", "logtime" : "2016-05-01 02:05:06 -0500", "user" : "bdmin"}  
{"hostname" : "node1", "level" : "info", "log" : "this is dummy log1", "logtime" : "2016-05-01 02:03:06 -0500", "user" : "admin"}  
{"hostname" : "node1", "level" : "info", "log" : "this is dummy log1", "logtime" : "2016-05-01 02:07:06 -0500", "user" : "gdmin"}  
{"hostname" : "node1", "level" : "info", "log" : "this is dummy log1", "logtime" : "2016-05-01 02:01:06 -0500", "user" : "cdmin"}

When I do a search of docs and sort on field "logtime", the results are not sorted and the score value suggests as if all docs were inserted with same timestamp which is not the one contained in logtime.  
Query:  
curl -XGET "localhost:9200/myindex/type1/\_search?sort=logtime:desc&fields=logtime,user&pretty"  
Result:  
"fields" : { "logtime" : ["2016-05-01 02:05:06 -0500"], "user" : ["bdmin"] }, "sort" : [1462078806000]}  
"fields" : { "logtime" : ["2016-05-01 02:03:06 -0500"], "user" : ["admin"] }, "sort" : [1462078806000]}  
"fields" : { "logtime" : ["2016-05-01 02:07:06 -0500"], "user" : ["gdmin"] }, "sort" : [1462078806000]}  
"fields" : { "logtime" : ["2016-05-01 02:01:06 -0500"], "user" : ["cdmin"] }, "sort" : [1462078806000]}

But if I do sort on field "user", the results are lexicographically sorted as expected. Query:  
curl -XGET "localhost:9200/myindex/type1/\_search?sort=user:desc&fields=logtime,user&pretty"  
Result =  
"fields" : {"logtime" : ["2016-05-01 02:07:06 -0500"],"user" : ["gdmin"]}, "sort" : ["gdmin"] }  
"fields" : {"logtime" : ["2016-05-01 02:01:06 -0500"],"user" : ["cdmin"]}, "sort" : ["cdmin"] }  
"fields" : {"logtime" : ["2016-05-01 02:05:06 -0500"],"user" : ["bdmin"]}, "sort" : ["bdmin"] }  
"fields" : {"logtime" : ["2016-05-01 02:03:06 -0500"],"user" : ["admin"]}, "sort" : ["admin"] }

Can anyone please help me in pointing out where am I going wrong or what am I missing here?  
In the docs, they have discussed about enabling \_timestamp but am not sure how to assign my field "logtime" to this \_timestamp field.  
PLEASE NOTE that my pipeline uses fluentd to insert docs in es.

Any help is appreciated.

Thanks.

---

<div class="post-metadata">

**Author:** ![Bruce\_Ritchie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bruce_ritchie/32/9370_2.png) [@Bruce\_Ritchie](https://discuss.elastic.co/u/Bruce_Ritchie)\
**Post date:** [May 2, 2016, 2:11pm UTC](https://discuss.elastic.co/t/sorting-es-results-based-on-a-json-field-value-of-type-date/48950/2 "2016-05-02T14:11:56Z")

</div>

> [@mohigupt](#):
>
> yyyy-MM-dd HH:mm:ss -HHmm

Your format is incorrect for the time zone. Instead of :ss -HHmm you should have :ss Z I believe.

You'll have to reindex to correct this in your existing data.

---

<div class="post-metadata">

**Author:** ![mohigupt](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mohigupt](https://discuss.elastic.co/u/mohigupt)\
**Post date:** [May 3, 2016, 6:33am UTC](https://discuss.elastic.co/t/sorting-es-results-based-on-a-json-field-value-of-type-date/48950/3 "2016-05-03T06:33:58Z")

</div>

Thanks Bruce. That worked 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 5, 2017, 10:54pm UTC](https://discuss.elastic.co/t/sorting-es-results-based-on-a-json-field-value-of-type-date/48950/4 "2017-07-05T22:54:30Z")

</div>