Select date range with first date criteria?

[Koen_Wouters]

Hi there, I know how to select a time range in elastic, but would it also be possible to select a time range defining a 'first date' setting.

Something like this totally fictional thing : { "range": {"min": {"my_date": { "gte": "2019-01-01" }}}}

So I would get all results for 2019, but only if the first entry was in 2019. Anything before should be ignored

[Glen_Smith]

Hello,

Yes, if you look at Range query, you will see that the "endpoints" are optional. Have you tried the search you describe?

[Koen_Wouters]

Not sure if I got it, can't really find it back in your link.

Just to make clear, if I have for example 2 products, and for one sales started in 2018 and ongoing, and for another sales started in 2019 and ongoing, and I would query for sales in 2019 I would get results for both.

But I only want to have returns for products where the first sale occurred in 2019, so excluding older ones.

[Glen_Smith]

Let me see if I understand your question.

Your data (loosely):

Product 1
Name: Widget
First sale: 2017-07-15

Product 2
Name: Jacket
First sale: 2018-07-15

Sale 1:
Product: 1
Date: 2017-08-01

Sale 2:
Product: 1
Date: 2018-04-28

Sale 3:
Product: 2
Date: 2018-04-28

You need to be able to query for all sales within a given time period, e.g. all of 2018, which, with this data, would return Sale 2 and Sale 3.

You also need to be able to query for all sales within a given time period exclusively for products whose first sale meets specific criteria, e.g. all sales in 2018 for products whose first sale happened no earlier than 2018-01-01, which, with this data, would return only Sale 3.

Is that correct?

[Koen_Wouters]

Hi @Glen_Smith, Indeed. The first part is easy, that's just a range.
The second part is where my struggle lies, how do I filter for products where the sales started within my range, so how do I filter out 'legacy' products still generating sales.

[Glen_Smith]

You could accomplish this by issuing multiple queries - first search for products with First Sale after your criterion date, then search for sales on the resulting product list, using a terms query.

Otherwise, what you will need to do is make the First Sale date available in the context of each sale of the product. You could accomplish this by adding the First Sale field to each Sale document, or by defining a Join relationship from Sales to Products. While the former approach, denormalization, obviously represents redundancy and hence increased storage usage, it also results in faster query performance and less complex queries.