elasticsearch find doc by time with datetime field-CodePudding

I'm trying to retrieve all documents that have a date between 2 dates and a time between 2 hours.

I can't get the query to work.

Is it possible ? If yes, how.

[
    {
        "_index": "a1",
        "_type": "_doc",
        "_id": "50c09e31-1fad-4d25-ab9d-35154a1b765b",
        "_score": 5.0,
        "_source":
        {
            "start_at": "2022-06-23 14:00",
            "end_at": "2022-06-23 14:15",
            ...
        }
    },
    {
        "_index": "a1",
        "_type": "_doc",
        "_id": "d96ba291-63de-422a-9123-3d1a1d573861",
        "_score": 5.0,
        "_source":
        {
            "start_at": "2022-06-24 16:30",
            "end_at": "2022-06-24 17:00",
            ...
        }
    }
]

GET /a1/_search?pretty { "query": { "bool": { "must": [ { "range": { "start_at": { "gte": "2022-06-20", "format": "yyyy-MM-dd" } } }, { "range": { "start_at": { "lt": "2022-06-27", "format": "yyyy-MM-dd" } } }, { "range": { "start_at": { "gte": "14:00", "format": "HH:mm" } } }, { "range": { "start_at": { "lt": "18:00", "format": "HH:mm" } } }, ] } }, "size": 10 }

Thanks.

CodePudding user response：

The immediate solution would be to use a query similar to this one but change the script part to:

doc['start_at'].value.getHourOfDay() ...

Since scripting can be bad for performance, a better solution would be to index the hours into a dedicated field and then perform a range query on it.