Elasticsearch how to search by time in datetime field

Question

I have a imaginary index test with below mapping

PUT test
{"mappings" : {
      "properties" : {
        "endTs" : {
          "type" : "date",
          "format": "yyyy-MM-dd HH:mm:ss",
          "index": true
        },
        "startTs" : {
          "type" : "date",
          "format": "yyyy-MM-dd HH:mm:ss",
          "index": true
        }
      }
    }
}

And add some value like below

POST _bulk
{ "index" : { "_index" : "test"} }
{ "startTs": "2020-01-01 00:00:00", "endTs": "2020-01-01 00:15:00" }
{ "index" : { "_index" : "test"} }
{ "startTs" : "2020-01-01 00:15:00", "endTs" : "2020-01-01 00:30:00" }
{ "index" : { "_index" : "test"} }
{ "startTs" : "2020-01-01 00:30:00", "endTs" : "2020-01-01 00:45:00" }
{ "index" : { "_index" : "test"} }
{ "startTs" : "2020-01-01 00:45:00", "endTs" : "2020-01-01 01:00:00" }
{ "index" : { "_index" : "test"} }
{ "startTs": "2020-01-01 01:00:00", "endTs": "2020-01-01 01:15:00" }
{ "index" : { "_index" : "test"} }
{ "startTs" : "2020-01-01 01:15:00", "endTs" : "2020-01-01 01:30:00" }
{ "index" : { "_index" : "test"} }
{ "startTs" : "2020-01-01 01:30:00", "endTs" : "2020-01-01 01:45:00" }
{ "index" : { "_index" : "test"} }
{ "startTs" : "2020-01-01 01:45:00", "endTs" : "2020-01-01 02:00:00" }

My goal is to run some range query within this startTs and endTs field. like startTs: 2020-01-01 00:00:00' and endTs: 2020-01-01 00:45:00'. I need to fo filter only with time not full datetime format. So I tried below filter

GET test/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "range": {
            "startTs": {
              "gte": "00:00:00",
              "format": "HH:mm:ss"
            }
          }
        },
        {
          "range": {
            "endTs": {
              "lte": "00:45:00",
              "format": "HH:mm:ss"
            }
          }
        }
      ]
    }
  }
}

I expected it will return all the record value less then equal to 2020-01-01 00:45:00

{ "startTs": "2020-01-01 00:00:00", "endTs": "2020-01-01 00:15:00" }
{ "startTs" : "2020-01-01 00:15:00", "endTs" : "2020-01-01 00:30:00" }
{ "startTs" : "2020-01-01 00:30:00", "endTs" : "2020-01-01 00:45:00" }

But It didn't return anything at all.

"hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }

Then I tried

GET test/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": {
              "source": "doc['startTs'].value.minute >= 0"
            }
          }
        },
        {
          "script": {
            "script": {
              "source": "doc['endTs'].value.minute <= 45"
            }
          }
        }
      ]
    }
  }
}

It return all the value that hase minute less than equal 45 and that is also not expected.

How to run the query for the given index with time range? any help is highly appreciated.

I am using elasticsearch 7.6

Answer 1

After lots of trail I found a solution.

It's working for me. Though I am not happy about it.

GET test/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": {
              "source": "doc['startTs'].value.hour * 60 + doc['startTs'].value.minute >= 0"
            }
          }
        },
        {
          "script": {
            "script": {
              "source": "doc['endTs'].value.hour * 60 + doc['endTs'].value.minute <= 45"
            }
          }
        }
      ]
    }
  }
}

Still looking for a better solution.