Query for documents with datetime field where time is outside office hours

Elastic Stack Elasticsearch
1

query for documents with datetime field where time is outside office hours.
There's a field vraag_datumtijd (same format as @timestamp) that's filled with a date time stamp of the moment of doing an user action and a field username. I would like to query the documents of yesterday and show the username that requests for documents outside office hours.

{ 
   "range": { 
      "vraag_datumtijd": { 
        "gte": "now-30d/d-1h", 
        "lte": "now-1d/d+6h" 
     } 
  } 
}

In some way I do receive results with above query as filter (in esql), but 2 problems I cannot get properly achieved.

  1. The time seems to differ with one hour (@timestamp field is 1 hour offset from vraag_datumtijd field (this might be the timezone difference)
  2. I would like to split the time as a separate filter, so that day range queried can be seen apart from the office hour filter (when using a range of more days, it still should filter for the office hours each day

Help will be appreciated. Thanks in advance.

2

Hi @Peter_K !

  1. The time seems to differ with one hour (@timestamp field is 1 hour offset from vraag_datumtijd field (this might be the timezone difference)

Yes, it seems like you're dealing with timezones here. You can provide the time_zone for range queries.

  1. I would like to split the time as a separate filter, so that day range queried can be seen apart from the office hour filter (when using a range of more days, it still should filter for the office hours each day

You could use a script filter for that, and separate it from your range query. Something similar to:

{
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": {
              "source": "doc['dateField'].value.getHour() >= params.startHour && doc['dateField'].value.getHour() < params.endHour",
              "params": {
                "startHour": 18,
                "endHour": 23
              }
            }
          }
        },
        {
          "range": {
            "dateField": {
              "gte": "now-7d/d",
              "lt": "now/d"
            }
          }
        }
      ]
    }
  }
}
3

Thanks for your reply. the filter works, but I'm not sure this matches my situation. Working day is from 6-23 hours. The ranges for the time filter therefore should be for document requests made between 23 till 24 hours and between 00 to 06 hours. Can you help me with an example of an OR construction of the filter that achieves this?

4

This is probably better achieved with negating the condition (not in working hours) to avoid date math.

You can use a boolean query with a must_not clause for the working hours, which is effectively used as a filter.

Something similar to:

{
  "query": {
    "bool": {
      "must_not": [
        {
          "script": {
            "script": {
              "source": "doc['dateField'].value.getHour() >= params.startHour && doc['dateField'].value.getHour() < params.endHour",
              "params": {
                "startHour": 6,
                "endHour": 23
              }
            }
          }
        }
      ],
      "filter": [
        {
          "range": {
            "dateField": {
              "gte": "now-7d/d",
              "lt": "now/d"
            }
          }
        }
      ]
    }
  }
}
1 Like
5

Great. That's working. Thank you very much for your help with this.
How can I close this topic or mark it as complete?

6

Glad it helped! You already closed it marking it as "Solution" :+1: