Elasticsearch date range query for range on a date-time field

Elastic Stack Elasticsearch
1

I have to get records (training) wherein I have a start_date, and I have to get all records that have not yet ended. So I don't have an end_date field, but just the start date, so end_date is start_date + 60 days. So what I need comprises all results that have "start_date" less than equal to "now" and "start_date + 60 days" greater than equal to "now" i.e. "now" is between start_date and "start_date + 60 days".

So here's what I am trying. But I know "on_demand_start_date+60d" is incorrect. Can anyone help, how can this be done?

"must": [
{
"range": {
"on_demand_start_date+60d": {
"gte": "now"
}
}
},
{
"range": {
"on_demand_start_date": {
"lte": "now"
}
}
}
]

Below is a sample document: { "_index": "cdc_sse_content", "_type": "doc", "_id": "cdc_sse_publish_1140", "_score": null, "_source": { "country": "us", "event_source_time_zone": "", "source": "CVENT", "iSAPIRecord": true, "index_type": "content", "content_title": "***Test - API March 04 - 1", "event_technology": [ "Cloud & Computing", "Collaboration", "Services (CX)" ], "language_code": "en", "application_name": "event-selector", "event_type": "Hybrid event", "event_region": [ "North America" ], "event_time_zone": "UTC", "cdc_page_url": "https://www.google.com?EID=<95929>", "event_end_date": "2022-02-05T01:00", "event_filters": "", "created_by": "shaiwsha", "live_stream_end_date": null, "marketing_owner": "tparida", "event_on-demand_details": "", "search_filter_lables": [ "On-demand" ], "kafka": { "consumer_group": "sseadmin", "partition": 0, "offset": 181002, "topic": "SSEAdminContentPublished", "key": null }, "updated_by": "shaiwsha", "publish_date": "2022-02-15", "event_start_date": "2022-02-04T17:00", "status": "active", "eid": "95929", "es_index_fail": false, "cdc_id": "event-selector_1140", "event_image_url": "https://www.cisco.com/c/dam/assets/events/i/event-selector/2021/rsa_global_events_page_br_dark_600x400.png", "description": "Test 1 - This is a sample Hybrid event, that should the event type,Hybrid with City and country displayed. This is a sample In-Person that should disp", "selector_id": "cdc_event-selector_publish_1140", "event_livestream_url": "", "locale": "en_us", "event_registration_url": "https://sandbox-www.cvent.com/d/p7qgmk/4W?&EID=95929", "event_live_stream_details": "", "event_city": "Santa Clara", "event_registration_and_location_details": "", "@version": "1", "event_details": "", "start_date": "2022-02-07T19:37", "registration_start_date": "2022-02-18T09:00", "event_industry": [ "Manufacturing" ], "event_language": [ "Cantonese", "Dutch", "English" ], "event_state": "California", "live_stream_start_date": null, "on_demand_url": "https://www.google.com", "on_demand": [ "On-demand" ], "@timestamp": "2022-03-15T07:28:08.821Z", "registration_end_date": "2022-02-20T09:00", "recent_search_published_date": "2022-03-10T14:17:30.320Z", "event_country": "USA", "featured_event": [ "Featured event" ], "created_date": "2022-03-10T10:08:05.801Z", "last_update_ts": "2022/3/10 14:19:5", "updated_date": "2022-03-10T14:19:05.693Z" }, "sort": [ 1643994000000 ] }

2

I think this is what you're looking for see here

This says less than or equal to now plus 60 days and greater than equal to now

"range": {
"on_demand_start_date": {
"lte" : "now+60d/d",
"gte": "now"
}
3

Hi Stephen,
thanks for the reply, but what I am looking for is basically calculate on on_deman_start_date, so like on_demand_start_date is "lte" now && "on_demand_start_date + 60days" is "gte" now.
I am not sure if what you suggested and what is expected both will be the same.

Just to explain, I don't have an end date for on_demand training, but the way it is expected is on_demand_end_date = on_demand_start_date + 60 days... so basically I need to restrict results based on if training has ended.
Thanks.

4

Ahh I think I see...

You could create a runtime field that represents

on_demand_start_date_plus_60d

and then use that in your query.

Or you could create that field when you ingest the data using it. Ingest pipeline and then use it in your query

5

So when I run the query: GET /
I get:
"version": {
"number": "5.6.16",
"build_hash": "3a740d1",
"build_date": "2019-03-13T15:33:36.565Z",
"build_snapshot": false,
"lucene_version": "6.6.1"
}
does runtime support this version? Also, will it impact the index anyway, because I don't want to touch the data and keep it as is? Also, do you have any samples of the runtime field? I tried doing so, but got an error.

6

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