# Change past dates to today's date while sorting in ElasticSearch

**URL:** <https://discuss.elastic.co/t/change-past-dates-to-todays-date-while-sorting-in-elasticsearch/81268>\
**Category:** Elasticsearch\
**Created:** [April 5, 2017, 7:57am UTC](https://discuss.elastic.co/t/change-past-dates-to-todays-date-while-sorting-in-elasticsearch/81268 "2017-04-05T07:57:50Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![hrishi\_es](https://avatars.discourse-cdn.com/v4/letter/h/8e8cbc/32.png) [@hrishi\_es](https://discuss.elastic.co/u/hrishi_es)\
**Post date:** [April 5, 2017, 7:57am UTC](https://discuss.elastic.co/t/change-past-dates-to-todays-date-while-sorting-in-elasticsearch/81268/1 "2017-04-05T07:57:50Z")

</div>

I am trying to sort Elasticsearch index on a date field in asc order. I have two conditions which I need to take care of while sorting.

1. All past dates should be considered as today's date. This matters since I am sorting on multiple fields.

2. All null values should be at the end.

I went through different suggestions of using function\_score and script\_score. I am new to Elasticsearch and I am unable to make that work for my usecase

So my query without applying any of above conditions look like this.

> POST /index/type/\_search  
> {  
> "query" : {  
> "term" : { "Id" : "12345" }  
> },  
> "sort" : {  
> "date" : {"order" : "asc"},  
> "sales" : {"order" : "desc"}  
> }  
> }

---

<div class="post-metadata">

**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\
**Post date:** [April 5, 2017, 1:48pm UTC](https://discuss.elastic.co/t/change-past-dates-to-todays-date-while-sorting-in-elasticsearch/81268/2 "2017-04-05T13:48:04Z")

</div>

First: wrap code in ``` so it is more readable.

For the "null values should be at the end" thing you probably want the [`missing`](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-sort.html#_missing_values) support from sorting.

For the date rules maybe you want script based sorting? It sounds like you have some rules about how you combine dates, or at least how you clamp their values. If you use script based scoring you'll have to pass `now` in as a parameter to the script. We intentionally don't expose that at the moment. Also, I'm not sure how script based scoring combines with the field scoring. That is a thing to experiment with.

---

<div class="post-metadata">

**Author:** ![hrishi\_es](https://avatars.discourse-cdn.com/v4/letter/h/8e8cbc/32.png) [@hrishi\_es](https://discuss.elastic.co/u/hrishi_es)\
**Post date:** [April 5, 2017, 6:53pm UTC](https://discuss.elastic.co/t/change-past-dates-to-todays-date-while-sorting-in-elasticsearch/81268/3 "2017-04-05T18:53:49Z")

</div>

Hi Nik,  
Thanks for responding.

what do you mean "We intentionally don't expose that at the moment" ? Does this mean i need to look for workaround it ?

I am looking to implement it with Java and have Elatsicsearch version 5.1 I'll experiment with it today and post my findings on how it works.

---

<div class="post-metadata">

**Author:** ![hrishi\_es](https://avatars.discourse-cdn.com/v4/letter/h/8e8cbc/32.png) [@hrishi\_es](https://discuss.elastic.co/u/hrishi_es)\
**Post date:** [April 8, 2017, 3:53am UTC](https://discuss.elastic.co/t/change-past-dates-to-todays-date-while-sorting-in-elasticsearch/81268/4 "2017-04-08T03:53:57Z")

</div>

> [@hrishi\_es](#):
>
> POST /index/type/\_search{ "query" : { "term" : { "Id" : "12345" } }, "sort" : { "date" : {"order" : "asc"}, "sales" : {"order" : "desc"} }}

Posting here my solution just in case someone else needs it.

> POST /inventoryplanningitems/inventoryplanningitem/\_search  
> {  
> "query" : {  
> "bool" : {  
> "must": [{  
> "match": {  
> "Id": "522291442"  
> }  
> }, {  
> "match": {  
> "status": "active"  
> }  
> }]  
> }  
> },  
> "sort": {  
> "\_script": {  
> "type": "string",  
> "order": "asc",  
> "script": {  
> "lang": "painless",  
> "inline": "def sf = new SimpleDateFormat("yyyy-MM-dd"); def today=sf.parse('2017-04-04'); if(doc['date'].value \> today.getTime()) {doc['date'].value} else {if(doc['date'].value == 0) {99999999999L} else {today.getTime()}"  
> }  
> },  
> "sales" : {"order" : "desc"}  
> }  
> }

---

<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:** [May 6, 2017, 4:02am UTC](https://discuss.elastic.co/t/change-past-dates-to-todays-date-while-sorting-in-elasticsearch/81268/5 "2017-05-06T04:02:24Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.