# Date field sorting problem **URL:** \ **Category:** Elasticsearch\ **Created:** [September 30, 2011, 5:16pm UTC](https://discuss.elastic.co/t/date-field-sorting-problem/5490 "2011-09-30T17:16:44Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![BBanzai](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@BBanzai](https://discuss.elastic.co/u/BBanzai)\ **Post date:** [September 30, 2011, 5:16pm UTC](https://discuss.elastic.co/t/date-field-sorting-problem/5490/1 "2011-09-30T17:16:44Z")
Hi all.. thanks in advance... I have what appears to be a date sorting issue. I run the following \_search query {"fields":["publish\_date"],"sort":[{"publish\_date": {"order":"desc"}}],"query":{"match\_all":{}}} I get back publish\_date sorted by what looks like day.. { ``` took: 3 timed_out: false _shards: { total: 5 successful: 5 failed: 0 } hits: { total: 269 max_score: null hits: [ { _index: products _type: xxxx _id: 10-XXXX _score: null fields: { publish_date: 2011-06-28 16:05:50 -0000 } sort: [ 1296230750000 ] } { _index: products _type: xxxx _id: 10-YYYY _score: null fields: { publish_date: 2011-02-27 01:01:24 -0000 } sort: [ 1296090084000 ] } { _index: products _type: xxxx _id: 10-ZZZZ _score: null fields: { publish_date: 2011-02-27 01:00:16 -0000 } sort: [ 1296090016000 ] } { _index: products _type: xxxx _id: 10-AAAA _score: null fields: { publish_date: 2011-02-27 00:58:48 -0000 } sort: [ 1296089928000 ] }, ``` { ``` _index: products _type: xxxx _id: 10-CCCC _score: null fields: { publish_date: 2011-07-21 22:20:35 -0000 } sort: [ 1295648435000 ] ``` } ... and so on. my mapping looks like: publish\_date: { format: "yyyy-mm-dd HH:mm:ss Z" type: "date" } I load the data via \_bulk inside the bulk import (JSON) file.. the publish\_date looks like: ,"publish\_date":"2011-07-21 22:20:35 -0000", what could be going wrong? ---
**Author:** ![BBanzai](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@BBanzai](https://discuss.elastic.co/u/BBanzai)\ **Post date:** [September 30, 2011, 6:23pm UTC](https://discuss.elastic.co/t/date-field-sorting-problem/5490/2 "2011-09-30T18:23:07Z")
Here is the process to reproduce the problem: curl -XDELETE '[http://localhost:9200/products](http://localhost:9200/products)' curl -XPUT '[http://localhost:9200/products](http://localhost:9200/products)' -d @testmapping.json testmapping.json: { "mappings": { "mytype": { "date\_formats": [ "yyyy-mm-dd HH:mm:ss" ], "dynamic": "true", "properties": { "publish\_date": { "type": "date", "format": "yyyy-mm-dd" }, "docid": { "type": "string", "index": "analyzed", "analyzer": "exact\_analyzer" }, "title": { "type": "string", "analyzer": "std\_stem\_analyzer" } } } } } curl -XPOST '[http://localhost:9200/\_bulk](http://localhost:9200/_bulk)' --data-binary @bulk\_1.json bulk\_1.json: {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : "10-0001" }} {"publish\_date":"2011-02-27","docid":"10-0001",title:"expect at 3"} {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : "10-0002" }} {"publish\_date":"2011-09-15","docid":"10-0002",title:"expect at 1"} {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : "10-0003" }} {"publish\_date":"2011-06-10","docid":"10-0003",title:"expect at 2"} $ curl -XGET '[http://localhost:9200/products/mytype/\_search?pretty=true](http://localhost:9200/products/mytype/_search?pretty=true)' -d ' { "sort": [ { "publish\_date": { "order": "desc" } } ], "query": { "match\_all": {} } } ' You should get back this: { "took" : 4, "timed\_out" : false, "\_shards" : { "total" : 5, "successful" : 5, "failed" : 0 }, "hits" : { "total" : 3, "max\_score" : null, "hits" : [ { "\_index" : "products", "\_type" : "threatintel", "\_id" : "10-0001", "\_score" : null, "\_source" : {"publish\_date":"2011-02-27","docid":"10-0001",title:"expect at 3"}, "sort" : [1296086520000] }, { "\_index" : "products", "\_type" : "threatintel", "\_id" : "10-0002", "\_score" : null, "\_source" : {"publish\_date":"2011-09-15","docid":"10-0002",title:"expect at 1"}, "sort" : [1295050140000] }, { "\_index" : "products", "\_type" : "threatintel", "\_id" : "10-0003", "\_score" : null, "\_source" : {"publish\_date":"2011-06-10","docid":"10-0003",title:"expect at 2"}, "sort" : [1294617960000] } ] } Note: I have tried various date formats with same result. ---
**Author:** ![ppearcy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ppearcy/32/980_2.png) [@ppearcy](https://discuss.elastic.co/u/ppearcy)\ **Post date:** [September 30, 2011, 7:18pm UTC](https://discuss.elastic.co/t/date-field-sorting-problem/5490/3 "2011-09-30T19:18:22Z")
Hey, Your date format is not defined correctly. mm == minute MM == month This page is linked from the date format section of the guide: [http://joda-time.sourceforge.net/api-release/org/joda/time/format/DateTimeFormat.html](http://joda-time.sourceforge.net/api-release/org/joda/time/format/DateTimeFormat.html) Best Regards, Paul On Sep 30, 12:23 pm, BBanzai [prigor2...@mac.com](mailto:prigor2...@mac.com) wrote: > Here is the process to reproduce the problem: > > curl -XDELETE '[http://localhost:9200/products](http://localhost:9200/products)' > > curl -XPUT '[http://localhost:9200/products'-d](http://localhost:9200/products'-d) @testmapping.json > testmapping.json: > { > "mappings": { > "mytype": { > "date\_formats": [ > "yyyy-mm-dd HH:mm:ss" > ], > "dynamic": "true", > "properties": { > "publish\_date": { > "type": "date", > "format": "yyyy-mm-dd" > }, > "docid": { > "type": "string", > "index": "analyzed", > "analyzer": "exact\_analyzer" > }, > "title": { > "type": "string", > "analyzer": "std\_stem\_analyzer" > } > } > } > } > > } > > curl -XPOST '[http://localhost:9200/\_bulk'--data-binary](http://localhost:9200/_bulk'--data-binary) @bulk\_1.json > > bulk\_1.json: > > {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : > "10-0001" }} > {"publish\_date":"2011-02-27","docid":"10-0001",title:"expect at 3"} > {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : > "10-0002" }} > {"publish\_date":"2011-09-15","docid":"10-0002",title:"expect at 1"} > {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : > "10-0003" }} > {"publish\_date":"2011-06-10","docid":"10-0003",title:"expect at 2"} > > $ > > curl -XGET '[http://localhost:9200/products/mytype/\_search?pretty=true](http://localhost:9200/products/mytype/_search?pretty=true)' > -d ' > { > "sort": [ > { > "publish\_date": { > "order": "desc" > } > } > ], > "query": { > "match\_all": {} > }} > > ' > > You should get back this: > > { > "took" : 4, > "timed\_out" : false, > "\_shards" : { > "total" : 5, > "successful" : 5, > "failed" : 0 > }, > "hits" : { > "total" : 3, > "max\_score" : null, > "hits" : [ { > "\_index" : "products", > "\_type" : "threatintel", > "\_id" : "10-0001", > "\_score" : null, "\_source" : > {"publish\_date":"2011-02-27","docid":"10-0001",title:"expect at 3"}, > "sort" : [1296086520000] > }, { > "\_index" : "products", > "\_type" : "threatintel", > "\_id" : "10-0002", > "\_score" : null, "\_source" : > {"publish\_date":"2011-09-15","docid":"10-0002",title:"expect at 1"}, > "sort" : [1295050140000] > }, { > "\_index" : "products", > "\_type" : "threatintel", > "\_id" : "10-0003", > "\_score" : null, "\_source" : > {"publish\_date":"2011-06-10","docid":"10-0003",title:"expect at 2"}, > "sort" : [1294617960000] > } ] > } > > Note: I have tried various date formats with same result. ---
**Author:** ![BBanzai](https://avatars.discourse-cdn.com/v4/letter/b/4af34b/32.png) [@BBanzai](https://discuss.elastic.co/u/BBanzai)\ **Post date:** [September 30, 2011, 7:26pm UTC](https://discuss.elastic.co/t/date-field-sorting-problem/5490/4 "2011-09-30T19:26:01Z")
Paul.. Great catch! I had been staring at this for a couple of hours.. I am no stranger to data formatting.. but for some reason.. I just didn't see it.. wow.. looks so obvious now!!! Thanks for finding it.. -Paul... too. On Sep 30, 12:18 pm, ppearcy [ppea...@gmail.com](mailto:ppea...@gmail.com) wrote: > Hey, > Your date format is not defined correctly. > > mm == minute > MM == month > > This page is linked from the date format section of the guide:[http://joda-time.sourceforge.net/api-release/org/joda/time/format/Dat](http://joda-time.sourceforge.net/api-release/org/joda/time/format/Dat)... > > Best Regards, > Paul > > On Sep 30, 12:23 pm, BBanzai [prigor2...@mac.com](mailto:prigor2...@mac.com) wrote: > > > Here is the process to reproduce the problem: > > > curl -XDELETE '[http://localhost:9200/products](http://localhost:9200/products)' > > > curl -XPUT '[http://localhost:9200/products'-d@testmapping.json](http://localhost:9200/products'-d@testmapping.json) > > testmapping.json: > > { > > "mappings": { > > "mytype": { > > "date\_formats": [ > > "yyyy-mm-dd HH:mm:ss" > > ], > > "dynamic": "true", > > "properties": { > > "publish\_date": { > > "type": "date", > > "format": "yyyy-mm-dd" > > }, > > "docid": { > > "type": "string", > > "index": "analyzed", > > "analyzer": "exact\_analyzer" > > }, > > "title": { > > "type": "string", > > "analyzer": "std\_stem\_analyzer" > > } > > } > > } > > } > > > } > > > curl -XPOST '[http://localhost:9200/\_bulk'--data-binary@bulk\_1.json](http://localhost:9200/_bulk'--data-binary@bulk_1.json) > > > bulk\_1.json: > > > {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : > > "10-0001" }} > > {"publish\_date":"2011-02-27","docid":"10-0001",title:"expect at 3"} > > {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : > > "10-0002" }} > > {"publish\_date":"2011-09-15","docid":"10-0002",title:"expect at 1"} > > {"create" : { "\_index" : "products", "\_type" : "mytype", "\_id" : > > "10-0003" }} > > {"publish\_date":"2011-06-10","docid":"10-0003",title:"expect at 2"} > > > $ > > > curl -XGET '[http://localhost:9200/products/mytype/\_search?pretty=true](http://localhost:9200/products/mytype/_search?pretty=true)' > > -d ' > > { > > "sort": [ > > { > > "publish\_date": { > > "order": "desc" > > } > > } > > ], > > "query": { > > "match\_all": {} > > }} > > > ' > > > You should get back this: > > > { > > "took" : 4, > > "timed\_out" : false, > > "\_shards" : { > > "total" : 5, > > "successful" : 5, > > "failed" : 0 > > }, > > "hits" : { > > "total" : 3, > > "max\_score" : null, > > "hits" : [ { > > "\_index" : "products", > > "\_type" : "threatintel", > > "\_id" : "10-0001", > > "\_score" : null, "\_source" : > > {"publish\_date":"2011-02-27","docid":"10-0001",title:"expect at 3"}, > > "sort" : [1296086520000] > > }, { > > "\_index" : "products", > > "\_type" : "threatintel", > > "\_id" : "10-0002", > > "\_score" : null, "\_source" : > > {"publish\_date":"2011-09-15","docid":"10-0002",title:"expect at 1"}, > > "sort" : [1295050140000] > > }, { > > "\_index" : "products", > > "\_type" : "threatintel", > > "\_id" : "10-0003", > > "\_score" : null, "\_source" : > > {"publish\_date":"2011-06-10","docid":"10-0003",title:"expect at 2"}, > > "sort" : [1294617960000] > > } ] > > } > > > Note: I have tried various date formats with same result. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:53am UTC](https://discuss.elastic.co/t/date-field-sorting-problem/5490/5 "2017-07-06T03:53:14Z")