I want to store time-related data in ElasticSearch. Each document has a start time and an end time which define when it was relevant (an open ticket for example). Then I want to be able to run queries such as:
- Show all tickets that were open between January 5th to February 2th.
- Show facets on multiple fields for all tickets opened in some time range
Since I'll have monthly indexes, documents spanning more than a month will be stored in more than one index.
For example, a ticket opened between January and April needs to be stored in the indexes of all 4 months' indexes.
I was wondering if there is an easy way to run later on aggregations across the indexes which will know to take each ticket only once into account (for faceting and such).
For example, if I have the following tickets:
- Ticket A is open between 1/1/2021-17/1/2021
- Ticket B is open between 15/1/2021-16/2/2021
- Ticket C is open between 12/1/2021-16/3/2021
We will have the following documents/indexes:
Index January:
{id: A, StartDate: 1/1/2021, EndDate: 17/1/2021}
{id: B, StartDate: 15/1/2021}
{id: C, StartDate: 12/1/2021}
Index February:
{id: B, StartDate: 15/1/2021, EndDate: 16/2/2021}
{id: C, StartDate: 12/1/2021}
Index March:
{id: C, StartDate: 12/1/2021, endDate: 16/3/2021}
Any inputs related to the best way to implement such a thing with ElasticSearch are most welcomed. If there are other preferred databases for the job with high scale and fast aggregations I'll be happy to hear on alternatives as well.