# Sorting on a date field **URL:** \ **Category:** Elasticsearch\ **Created:** [November 10, 2010, 12:41am UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542 "2010-11-10T00:41:47Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![John\_Chang](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@John\_Chang](https://discuss.elastic.co/u/John_Chang)\ **Post date:** [November 10, 2010, 12:41am UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/1 "2010-11-10T00:41:47Z")
I need to query docs, having results sorted by a date field. Before using Elastic Search, I cast my date (long of millis since epoc) to a float and then sorted on that. I did the cast because I read that Lucene could not sort on a long. There was a loss of precision, obviously, and if the docs were too close in time, the ordering might be off, which we tolerated. When we switched to Elastic Search, we did the same thing, doing a script on the timestamp saved as a float; again it worked with tolerable some loss of precision. Now I want to improve the precision. In a different thread you advised me to to save the date as a long and the do a script on that. I find it also generally works but also lacks precision when the dates get too close (generally under 3 minutes or so apart). This is what I did (sortField referenced a long): response = indexClient.search(Requests.searchRequest(getIndexName()).types(documentTypes).searchType(SearchType.QUERY\_THEN\_FETCH).source(SearchSourceBuilder.searchSource().query(QueryBuilders.customScoreQuery(QueryBuilders.queryString(query)).script("doc['" + sortField + "'].value")).fields(fields).from(offset).size(max).explain(true))).actionGet(); I also tried storing the field as a string and doing a sort on that. It worked and the precision was better, but I could not get the sort order param to work -- I get the same results whether I user SortOrder.ASC or DESC. This is what I did (sortField referenced a string): response = indexClient.search(Requests.searchRequest(getIndexName()).types(documentTypes).searchType(SearchType.DFS\_QUERY\_THEN\_FETCH).source(SearchSourceBuilder.searchSource().query(QueryBuilders.queryString(query)).fields(fields).from(offset).size(max).explain(true).sort(sortField, SortOrder.DESC))).actionGet(); 1. Is loss of precision doing a script on a long expected? Is there anything I can do to improve precision? 2. If I wind up doing a sort on a string, how can I get the sort param to work? 3. I understand a sort is slower than a script; how much worse is this expected to be? BTW #1: I know a script will change the score and a sort will not. I'm not too worried about that. BTW #2: I need to use the from/size params for pagination; not sure if that impacts this decision. Thanks. ---
**Author:** ![John\_Chang](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@John\_Chang](https://discuss.elastic.co/u/John_Chang)\ **Post date:** [November 10, 2010, 12:52am UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/2 "2010-11-10T00:52:52Z")
Perhaps a little more context might help: Before I tolerated the loss of precision because my search was not paginated. I just returned the top N docs, starting at 0. I could tolerate docs a little out of order or do my own second sort on the result set to get it perfect. Now I am paginating. So, if I got 30 docs close in time, and my page size is 10, my pages will be wierd; the same doc can show up on pages 1, 2, and 3, and some docs might now show up on any. Thanks again. ---
**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:** [November 10, 2010, 6:56am UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/3 "2010-11-10T06:56:55Z")
Hey John, Maybe I am missing some context, as you refer to a previous thread, but I'm sorting on date fields with no problem and they appear to have a 1 second granularity. Thanks, Paul On Nov 9, 5:52 pm, John Chang [jchangkihte...@gmail.com](mailto:jchangkihte...@gmail.com) wrote: > Perhaps a little more context might help: > > Before I tolerated the loss of precision because my search was not > paginated. I just returned the top N docs, starting at 0. I could tolerate > docs a little out of order or do my own second sort on the result set to get > it perfect. > > Now I am paginating. So, if I got 30 docs close in time, and my page size > is 10, my pages will be wierd; the same doc can show up on pages 1, 2, and > 3, and some docs might now show up on any. > > ## Thanks again. > > View this message in context:[http://elasticsearch-users.115913.n3.nabble.com/Sorting-on-a-date-fie](http://elasticsearch-users.115913.n3.nabble.com/Sorting-on-a-date-fie)... > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [November 10, 2010, 11:00am UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/4 "2010-11-10T11:00:14Z")
Hi, There is no problem to sort on long (in Lucene or Elasticsearch). You can simply index a long value up to 1 millisecond resolution (for example), and then sort on it. The `date` type in elasticsearch actually indexes a long value, parsing the relevant date to a long. The resolution of the long value is based on the date string passed. When you do sort, don't use scripting based sorting for that, just add the field to be sorted, its type will be autodetected and the proper sorting will be done. -shay.banon On Wed, Nov 10, 2010 at 8:56 AM, Paul [ppearcy@gmail.com](mailto:ppearcy@gmail.com) wrote: > Hey John, > Maybe I am missing some context, as you refer to a previous thread, > but I'm sorting on date fields with no problem and they appear to have > a 1 second granularity. > > Thanks, > Paul > > On Nov 9, 5:52 pm, John Chang [jchangkihte...@gmail.com](mailto:jchangkihte...@gmail.com) wrote: > > > Perhaps a little more context might help: > > > > Before I tolerated the loss of precision because my search was not > > paginated. I just returned the top N docs, starting at 0. I could > > tolerate > > docs a little out of order or do my own second sort on the result set to > > get > > it perfect. > > > > Now I am paginating. So, if I got 30 docs close in time, and my page > > size > > is 10, my pages will be wierd; the same doc can show up on pages 1, 2, > > and > > 3, and some docs might now show up on any. > > > > ## Thanks again. > > > > View this message in context: > > [http://elasticsearch-users.115913.n3.nabble.com/Sorting-on-a-date-fie](http://elasticsearch-users.115913.n3.nabble.com/Sorting-on-a-date-fie)... > > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![John\_Chang](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@John\_Chang](https://discuss.elastic.co/u/John_Chang)\ **Post date:** [November 10, 2010, 10:27pm UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/5 "2010-11-10T22:27:18Z")
I'm afraid I must not be understanding your advice. I guess I need more specifics about how you want me to (A) map, (B) index and (C) search the doc. I read your response and then.... I tried mapping the field this way: "receivedDate" : { "index\_name": "receivedDate", "type": "date", "index": "analyzed", "store": "yes", "term\_vector": "no", "boost": 1.0, "omit\_norms": "false", "omit\_term\_freq\_and\_positions": "false" } I then tried these combinations (data.getReceivedDateInUTC() returns a java.util.Date): 1. Map\ document = new HashMap\(); document.put("receivedDate", data.getReceivedDateInUTC()); When I searched on the doc with a script: .script("doc['" + sortField + "'].value") it worked, but lacked precision with dates close in time (under ~ 3 min). 1.1) I indexed the doc as in 1 above, and I searched on the doc with a sort: .sort(sortField) I got SearchPhaseExecutionException 1. Map\ document = new HashMap\(); document.put("receivedDate", data.getReceivedDateInUTC().getTime()); I could not index the doc; I got: java.lang.IllegalArgumentException: Invalid format: "1204351200000" is malformed at "0000" Didn't expect this to work, but was taking a guess because nothing else did. 1. Map\ document = new HashMap\(); document.put("receivedDate", Long.toString(data.getReceivedDateInUTC().getTime())); I could not index the doc; I got: java.lang.IllegalArgumentException: Invalid format: "1204351200000" is malformed at "0000" (same as #2 above) Your response mentioned a date string: "The resolution of the long value is based on the date string passed." Hence this test. 1. Map\ document = new HashMap\(); document.put("receivedDate", data.getReceivedDateInUTC().toString()); I could not index the doc; I got: org.elasticsearch.index.mapper.MapperParsingException: Failed to parse [receivedDate] As in 3 above, this was a test based on my understanding of a date string from your response. Thanks for your help. ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [November 10, 2010, 11:03pm UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/6 "2010-11-10T23:03:30Z")
You have two options, first is to have a numeric (long) type for the field, second is to have a date type, which expects (by default) an ISO formatted date string. If you use the Java API, you have two options: 1. If you want the field to be numeric, then just add the "milliseconds since epoch" value (Date.getTime()) to your Map of values. 2. If you want the field to be a date type, then either provide the formatted string yourself, or, pass a Date instance as the value of the field, it will automatically be formatted to its ISO format and indexed. In both options, you don't need to explicitly set the mappings. It will be auto detected. I say, since you already have a Date object, to index it (which will, in turn, be formatted as ISO formatted string, and detected as such in the date type). Once you have index it, you will be able to sort it by just adding it as sort field in the search API. Regarding the resolution, I am not sure I understand what you mean by missing resolution, but make sure the data you index does actually offer the resolution you expected (the Date instance you get, does it have that resolution?). -shay.banon On Thu, Nov 11, 2010 at 12:27 AM, John Chang [jchangkihtest2@gmail.com](mailto:jchangkihtest2@gmail.com)wrote: > I'm afraid I must not be understanding your advice. I guess I need more > specifics about how you want me to (A) map, (B) index and (C) search the > doc. > > I read your response and then.... > > I tried mapping the field this way: > "receivedDate" : { > "index\_name": "receivedDate", > "type": "date", > "index": "analyzed", > "store": "yes", > "term\_vector": "no", > "boost": 1.0, > "omit\_norms": "false", > "omit\_term\_freq\_and\_positions": "false" > } > > I then tried these combinations (data.getReceivedDateInUTC() returns a > java.util.Date): > > 1. > > Map\ document = new HashMap\(); > document.put("receivedDate", data.getReceivedDateInUTC()); > > When I searched on the doc with a script: > .script("doc['" + sortField + "'].value") > it worked, but lacked precision with dates close in time (under ~ 3 min). > > 1.1) > I indexed the doc as in 1 above, and I searched on the doc with a sort: > .sort(sortField) > I got SearchPhaseExecutionException > > 1. > > Map\ document = new HashMap\(); > document.put("receivedDate", data.getReceivedDateInUTC().getTime()); > > I could not index the doc; I got: > java.lang.IllegalArgumentException: Invalid format: "1204351200000" is > malformed at "0000" > > Didn't expect this to work, but was taking a guess because nothing else > did. > > 1. > > Map\ document = new HashMap\(); > document.put("receivedDate", > Long.toString(data.getReceivedDateInUTC().getTime())); > > I could not index the doc; I got: > java.lang.IllegalArgumentException: Invalid format: "1204351200000" is > malformed at "0000" > (same as #2 above) > > Your response mentioned a date string: "The resolution of the long value is > based on the date string passed." Hence this test. > > 1. > > Map\ document = new HashMap\(); > document.put("receivedDate", data.getReceivedDateInUTC().toString()); > > I could not index the doc; I got: > org.elasticsearch.index.mapper.MapperParsingException: Failed to parse > [receivedDate] > > As in 3 above, this was a test based on my understanding of a date string > from your response. > > ## Thanks for your help. > > View this message in context: > [http://elasticsearch-users.115913.n3.nabble.com/Sorting-on-a-date-field-tp1873287p1879190.html](http://elasticsearch-users.115913.n3.nabble.com/Sorting-on-a-date-field-tp1873287p1879190.html) > Sent from the Elasticsearch Users mailing list archive at [Nabble.com](http://Nabble.com). ---
**Author:** ![John\_Chang](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@John\_Chang](https://discuss.elastic.co/u/John_Chang)\ **Post date:** [November 11, 2010, 6:44pm UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/7 "2010-11-11T18:44:19Z")
Thanks! I got my test working, mapping the field as a date. My problem before is that I was using a QueryBuilders.customScoreQuery with a sort, which produced the error. I got rid of the customScoreQuery part and the sort worked great. Unfortunately, my production dates are not mapped as dates; I foolishly mapped the long values as type string. Until I can get to reindexing the dates as date fields, is there any way I can get the sorting to work with precision having the longs indexed as strings? I'll need to be able to sort them both ascending and descending. The string value is mapped as index=no, store=yes in case that makes a difference. We don't have a way of reindexing quickly here - we are working on that. Thanks again! ---
**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, 4:16am UTC](https://discuss.elastic.co/t/sorting-on-a-date-field/3542/8 "2017-07-06T04:16:42Z")