# Current time dependent filter/query

**URL:** <https://discuss.elastic.co/t/current-time-dependent-filter-query/7901>\
**Category:** Elasticsearch\
**Created:** [May 30, 2012, 2:27am UTC](https://discuss.elastic.co/t/current-time-dependent-filter-query/7901 "2012-05-30T02:27:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![ywtsang](https://avatars.discourse-cdn.com/v4/letter/y/a698b9/32.png) [@ywtsang](https://discuss.elastic.co/u/ywtsang)\
**Post date:** [May 30, 2012, 2:27am UTC](https://discuss.elastic.co/t/current-time-dependent-filter-query/7901/1 "2012-05-30T02:27:49Z")

</div>

Hi,

I am evaluating to migrate our old search system using lucene to elastic  
search and I want to do a query/filter on a value of my document that  
depends on current time and multiple fields.

Let me describe by example:

Document: Product

Fields:  
list\_price / Double  
sale\_price / nullable Double  
sale\_price\_start / nullable Date  
sale\_price\_end / nullable Date

The logic/calculation of the "value" that I want to do query/filter is:  
sale\_price != null && (sale\_price\_start \<= NOW \<= sale\_price\_end) ?  
sale\_price : list\_price

Because "NOW" is changing, we cannot index the value as a stored field.

But I know we can do query / filter using script field to dynamically  
calculate the value. However the performance should be bad because it needs  
to calculate the value for all documents in the index.

More background:  
in our old days, we just index the calculated value and have another daily  
cron to find and reindex all documents which have the calculated values  
"expired" (because the sale\_price\_start and sale\_price\_end already expires)

so now I just want to see if there is any advice to model this problem in  
elasticsearch.

Thanks,  
Wing

---

<div class="post-metadata">

**Author:** ![Randall\_McRee](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/randall_mcree/32/47177_2.png) [@Randall\_McRee](https://discuss.elastic.co/u/Randall_McRee)\
**Post date:** [May 30, 2012, 3:47am UTC](https://discuss.elastic.co/t/current-time-dependent-filter-query/7901/2 "2012-05-30T03:47:59Z")

</div>

Your previous solution is still the best and perfectly applicable.

Sent from my iPhone

On May 29, 2012, at 7:27 PM, Yiu Wing TSANG [ywtsang@gmail.com](mailto:ywtsang@gmail.com) wrote:

> Hi,
> 
> I am evaluating to migrate our old search system using lucene to Elasticsearch and I want to do a query/filter on a value of my document that depends on current time and multiple fields.
> 
> Let me describe by example:
> 
> Document: Product
> 
> Fields:  
> list\_price / Double  
> sale\_price / nullable Double  
> sale\_price\_start / nullable Date  
> sale\_price\_end / nullable Date
> 
> The logic/calculation of the "value" that I want to do query/filter is:  
> sale\_price != null && (sale\_price\_start \<= NOW \<= sale\_price\_end) ? sale\_price : list\_price
> 
> Because "NOW" is changing, we cannot index the value as a stored field.
> 
> But I know we can do query / filter using script field to dynamically calculate the value. However the performance should be bad because it needs to calculate the value for all documents in the index.
> 
> More background:  
> in our old days, we just index the calculated value and have another daily cron to find and reindex all documents which have the calculated values "expired" (because the sale\_price\_start and sale\_price\_end already expires)
> 
> so now I just want to see if there is any advice to model this problem in elasticsearch.
> 
> Thanks,  
> Wing

---

<div class="post-metadata">

**Author:** ![Igor\_Motov](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/igor_motov/32/45193_2.png) [@Igor\_Motov](https://discuss.elastic.co/u/Igor_Motov)\
**Post date:** [May 31, 2012, 5:49pm UTC](https://discuss.elastic.co/t/current-time-dependent-filter-query/7901/3 "2012-05-31T17:49:01Z")

</div>

If you have some search criteria based on price: filter(price), you can  
formulate you search query like this:

(  
(sale\_price != null AND sale\_price\_start \<= NOW AND NOW \<=  
sale\_price\_end) AND filter(sale\_price)  
)  
OR  
(  
(sale\_price == null OR sale\_price\_start \> NOW OR sale\_price\_end \< NOW)  
AND filter(list\_price)  
)

That's not valid elasticsearch synax, but, hopefully, it demonstrates the  
idea.

On Tuesday, May 29, 2012 11:47:59 PM UTC-4, RKM wrote:

> Your previous solution is still the best and perfectly applicable.
> 
> Sent from my iPhone
> 
> On May 29, 2012, at 7:27 PM, Yiu Wing TSANG [ywtsang@gmail.com](mailto:ywtsang@gmail.com) wrote:
> 
> > Hi,
> > 
> > I am evaluating to migrate our old search system using lucene to elastic  
> > search and I want to do a query/filter on a value of my document that  
> > depends on current time and multiple fields.
> > 
> > Let me describe by example:
> > 
> > Document: Product
> > 
> > Fields:  
> > list\_price / Double  
> > sale\_price / nullable Double  
> > sale\_price\_start / nullable Date  
> > sale\_price\_end / nullable Date
> > 
> > The logic/calculation of the "value" that I want to do query/filter is:  
> > sale\_price != null && (sale\_price\_start \<= NOW \<= sale\_price\_end) ?  
> > sale\_price : list\_price
> > 
> > Because "NOW" is changing, we cannot index the value as a stored field.
> > 
> > But I know we can do query / filter using script field to dynamically  
> > calculate the value. However the performance should be bad because it needs  
> > to calculate the value for all documents in the index.
> > 
> > More background:  
> > in our old days, we just index the calculated value and have another  
> > daily cron to find and reindex all documents which have the calculated  
> > values "expired" (because the sale\_price\_start and sale\_price\_end already  
> > expires)
> > 
> > so now I just want to see if there is any advice to model this problem  
> > in elasticsearch.
> > 
> > Thanks,  
> > Wing

---

<div class="post-metadata">

**Author:** ![ywtsang](https://avatars.discourse-cdn.com/v4/letter/y/a698b9/32.png) [@ywtsang](https://discuss.elastic.co/u/ywtsang)\
**Post date:** [June 1, 2012, 4:08am UTC](https://discuss.elastic.co/t/current-time-dependent-filter-query/7901/4 "2012-06-01T04:08:07Z")

</div>

To better illustrate my idea, I am trying hard to build the "price"  
range filters and sorting similar to [endless.com](http://endless.com):

> **[Amazon.com: Clothing, Shoes &amp; Jewelry](https://www.amazon.com/amazon-fashion/b?ie=UTF8&node=7141123011)**
>
> Shop at Amazon Fashion for a wide selection of clothing, shoes, jewelry and watches for both men and women at Amazon.com. Free shipping and free returns on eligible items.

Now, I am able to build the query/filter based on multiple  
combinations of OR/AND/MISSING/EXIST filters as hinted by Igor Motov.

But when I go to build the range facet and sorting on the "calculated"  
price, I am stuck.

So I am back to think about how to effectively reindex the products  
when sale period expires as suggested by Randy, and I find that the  
update API can quickly mass update many records using scripts  
conveniently and I think I will go in this way. And if there is any  
better way, free feel to share, many thanks.

Wing

On Fri, Jun 1, 2012 at 1:49 AM, Igor Motov [imotov@gmail.com](mailto:imotov@gmail.com) wrote:

> If you have some search criteria based on price: filter(price), you can  
> formulate you search query like this:
> 
> (  
> (sale\_price != null AND sale\_price\_start \<= NOW AND NOW \<=  
> sale\_price\_end) AND filter(sale\_price)  
> )  
> OR  
> (  
> (sale\_price == null OR sale\_price\_start \> NOW OR sale\_price\_end \< NOW) AND  
> filter(list\_price)  
> )
> 
> That's not valid elasticsearch synax, but, hopefully, it demonstrates the  
> idea.
> 
> On Tuesday, May 29, 2012 11:47:59 PM UTC-4, RKM wrote:
> 
> > Your previous solution is still the best and perfectly applicable.
> > 
> > Sent from my iPhone
> > 
> > On May 29, 2012, at 7:27 PM, Yiu Wing TSANG [ywtsang@gmail.com](mailto:ywtsang@gmail.com) wrote:
> > 
> > > Hi,
> > > 
> > > I am evaluating to migrate our old search system using lucene to elastic  
> > > search and I want to do a query/filter on a value of my document that  
> > > depends on current time and multiple fields.
> > > 
> > > Let me describe by example:
> > > 
> > > Document: Product
> > > 
> > > Fields:  
> > > list\_price / Double  
> > > sale\_price / nullable Double  
> > > sale\_price\_start / nullable Date  
> > > sale\_price\_end / nullable Date
> > > 
> > > The logic/calculation of the "value" that I want to do query/filter is:  
> > > sale\_price != null && (sale\_price\_start \<= NOW \<= sale\_price\_end) ?  
> > > sale\_price : list\_price
> > > 
> > > Because "NOW" is changing, we cannot index the value as a stored field.
> > > 
> > > But I know we can do query / filter using script field to dynamically  
> > > calculate the value. However the performance should be bad because it needs  
> > > to calculate the value for all documents in the index.
> > > 
> > > More background:  
> > > in our old days, we just index the calculated value and have another  
> > > daily cron to find and reindex all documents which have the calculated  
> > > values "expired" (because the sale\_price\_start and sale\_price\_end already  
> > > expires)
> > > 
> > > so now I just want to see if there is any advice to model this problem  
> > > in elasticsearch.
> > > 
> > > Thanks,  
> > > Wing

---

<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 6, 2017, 3:26am UTC](https://discuss.elastic.co/t/current-time-dependent-filter-query/7901/5 "2017-07-06T03:26:04Z")

</div>