How to filter date field by days and hours separately

[Guylot]

Hi,
I want to create a query that returns all the data from last 3 days and only between 09:00 AM - 18:00 PM.
My date field contains both the date and the time of the day (For example "time" : "2019-08-06T06:40:55Z").
Can I do something like or how can I achieve this with this mapping (Elastic version 5.6)?

"query" :{
   "bool": {
     "must": [
       {
         "range": {
           "time": {
             "gte": "2019-08-04",
             "lte": "2019-08-06"
           }
         }
       },
       {
         "range": {
           "time": {
             "gte": "09:00:00",
             "lte": "18:00:00"
           }
         }
       }
     ]
   }
 }

[spinscale (Alexander Reelsen)]

hey,

the fastest solution in terms of speed would be to have a bool with three should clauses, where each of them represents a range query filtering for each day. You could also use scripting and extract the hours of the day of a date and compare those, but that would end up in a massive speed reduction as each hit needs to be checked.

Please take your time and read through the documentation how must and should work together, as they change their behaviour, if there are no must clauses (something that is needed in order to model an OR query).

[Guylot]

Thanks, I think ill go with the script since I don't think should will be good if I want larger date ranges than 3 days, and my query need to be generic and work with different range of dates and different range of time (Hours and Minutes).

I have this query:

{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": "doc.readingTimestamp.date.getHourOfDay() >= 9 && doc.readingTimestamp.date.getHourOfDay() <= 18"
          }
        }
      ]
    }
  }
}

But now how can I change it so the start time will be 09:30 instead of 09:00 and is it possible to consider timezone also in the script?

[spinscale (Alexander Reelsen)]

you just take the minute into account as well, then you can model sth like 9:30. The date is always stored as UTC, you need to calculate offsets yourself.

[Guylot]

But if I run this query:

{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": "doc.readingTimestamp.date.getHourOfDay() >= 9 && doc.readingTimestamp.date.getMinuteOfHour() >= 30 && doc.readingTimestamp.date.getHourOfDay() < 18"
          }
        }
      ]
    }
  }
}

Then I won't get any result where the minute is less then "30" (including 10:20 for example), and I want to get all the data between 09:30 and 18:00

[spinscale (Alexander Reelsen)]

how about searching for this

hour >= 10 && hour <= 18 || hour >= 9 && minute >= 30