How to query if a time is between two field values

Question

How do I search for documents that are between a start and end time? For example, I want to query the following document using a time only like "18:33" or "21:32". "18:33" would return the following document and "21:32" wouldn't. I don't care about the date part nor the secs.

{
  "my start time field": "2020-01-23T18:32:21.768Z",
  "my end time field": "2020-01-23T20:32:21.768Z"
}

I've reviewed: Using the range query with date fields. but I'm not sure how to only look at times. Also, I want to see if a time is between two fields, not if a field is between two times.

---

Essentially, the Elasticsearch equivalent of BETWEEN for SQL Server. Like this answer except I don't want to use the current time but a variable.

DECLARE @blah datetime2 = GETDATE()

SELECT  *
FROM Table1 T
WHERE CAST(@blah AS TIME) 
      BETWEEN cast(T.StartDate as TIME) AND cast(T.EndDate as TIME)

Answer 1

As per the suggestion from the OP and the link he provided which adheres to the laws of stackoverflow I'm providing the second solution in here:

Solution 2: Insert separate fields for hour minute as hh:mm

Note the format used which says hour_minute. You can find the list of formats available under the aforementioned link.

Basically you re-ingest the documents with a separate field that would have hour and minute values and execute range queries to get what you want.

Mapping:

PUT my_time_index
{
  "mappings": {
    "properties": {
      "start_time":{
        "type": "date",
        "format": "hour_minute"
      },
      "end_time":{
        "type": "date",
        "format": "hour_minute"
      }
    }
  }
}

Sample Document:

POST my_time_index/_doc/1
{
  "start_time": "18:32",
  "end_time": "20:32"
}

Query Request:

POST my_time_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "start_time": {
              "gte": "18:00"
            }
          }
        },
        {
          "range": {
            "end_time": {
              "lte": "21:00"
            }
          }
        }
      ]
    }
  }
}

Let me know if this helps!

Answer 2

Solution 1: Existing Date Format

Without changing and ingesting your hours and minutes separately, I've come up with the below solution and I don't think you would be happy with the way ES provides you the solution, but it certainly works.

I've created a sample mapping, document, the query and response based on the data you've provided.

Mapping:

PUT my_date_index
{
  "mappings": {
    "properties": {
      "start_time":{
        "type": "date",
        "format": "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
      },
      "end_time":{
        "type": "date",
        "format": "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"
      }
    }
  }
}

Sample Documents:

POST my_date_index/_doc/1
{
  "start_time": "2020-01-23T18:32:21.768Z",
  "end_time": "2020-01-23T20:32:21.768Z"
}

POST my_date_index/_doc/2
{
  "start_time": "2020-01-23T19:32:21.768Z",
  "end_time": "2020-01-23T20:32:21.768Z"
}

POST my_date_index/_doc/3
{
  "start_time": "2020-01-23T21:32:21.768Z",
  "end_time": "2020-01-23T22:32:21.768Z"
}

Query Request:

POST my_date_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": {
              "source": """
                ZonedDateTime zstart_time = doc['start_time'].value;
                int zstart_hour = zstart_time.getHour();
                int zstart_minute = zstart_time.getMinute();
                int zstart_total_minutes = zstart_hour * 60 + zstart_minute;

                ZonedDateTime zend_time = doc['end_time'].value;
                int zend_hour = zend_time.getHour();
                int zend_minute = zend_time.getMinute();
                int zend_total_minutes = zend_hour * 60 + zend_minute;

                int my_input_total_minutes = params.my_input_hour * 60 + params.my_input_minute;

                if(zstart_total_minutes <= my_input_total_minutes && zend_total_minutes >= my_input_total_minutes){
                  return true;
                }

                return false;

              """,
              "params": {
                "my_input_hour": 20,
                "my_input_minute": 10
              }
            }
          }
        }
      ]
    }
  }
}

Basically

• calculate number of minutes from start_date

• calculate number of minutes from end_date

• calculate number of minutes from params.my_input_hour & params.my_input_minute

• execute the logic in if condition as start_date <= input <= end_date using the minutes of all the three values and return the documents accordingly.

Response:

{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 2.0,
    "hits" : [
      {
        "_index" : "my_time_index",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 2.0,
        "_source" : {
          "start_time" : "18:32",
          "end_time" : "20:32"
        }
      }
    ]
  }
}

Do test them thoroughly for performance issues when it comes to solution 1 as script queries generally hit performances, however they come in handy if you have no option.

Let me know if this helps!

Answer 3

Don't store times in a datetime datatype based upon this discussion.

If you want to filter for the specific hour of the day, you would need to extract that into it's own field.

Via the Kibana Dev Tools -> Console

Create some mock data:

POST between-research/_doc/1
{
  "my start hour": 0,
  "my end hour": 12
}

POST between-research/_doc/2
{
  "my start hour": 13,
  "my end hour": 23
}

Perform "between" search

POST between-research/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "my start hour": {
              "lte": 10
            }
          }
        },
        {
          "range": {
            "my end hour": {
              "gte": 10
            }
          }
        }
      ]
    }
  }
}