# How to filter date field by days and hours separately

**URL:** <https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946>\
**Category:** Elasticsearch\
**Created:** [August 6, 2019, 7:17am UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946 "2019-08-06T07:17:24Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Guylot](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guylot/32/47141_2.png) [@Guylot](https://discuss.elastic.co/u/Guylot)\
**Post date:** [August 6, 2019, 7:17am UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946/1 "2019-08-06T07:17:24Z")

</div>

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"
           }
         }
       }
     ]
   }
 }
```

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 6, 2019, 8:41am UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946/2 "2019-08-06T08:41:42Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![Guylot](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guylot/32/47141_2.png) [@Guylot](https://discuss.elastic.co/u/Guylot)\
**Post date:** [August 6, 2019, 11:57am UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946/3 "2019-08-06T11:57:02Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 6, 2019, 12:03pm UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946/4 "2019-08-06T12:03:16Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Guylot](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guylot/32/47141_2.png) [@Guylot](https://discuss.elastic.co/u/Guylot)\
**Post date:** [August 6, 2019, 12:14pm UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946/5 "2019-08-06T12:14:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [August 6, 2019, 9:33pm UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946/6 "2019-08-06T21:33:19Z")

</div>

how about searching for this

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [September 3, 2019, 9:42pm UTC](https://discuss.elastic.co/t/how-to-filter-date-field-by-days-and-hours-separately/193946/7 "2019-09-03T21:42:47Z")

</div>

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