Search by datetime range query

[rehannali (Rehan Ali)]

Hi, I have issue related to search through datetime range query. I tried to search some data which is oldest after a certain period. I read documentation and try to make query and I'm unable to get any of the data.

GET /my-index/_search
{
  "query": {
    "range": {
      "datetime": {
        "lt": "now-3d/d"
      }
    }
  }
}

It is working fine if I use only now in query.

GET /my-index/_search
{
  "query": {
    "range": {
      "datetime": {
        "lt": "now"
      }
    }
  }
}

The actual response if I use with now and I have enough data of last week so it should work.

{
        "_index" : "my-index",
        "_type" : "_doc",
        "_id" : "96234123102",
        "_score" : 1.0,
        "_source" : {
          "service" : "ONEZAPP",
          "description" : "CUSTOMER PORTAL FUND TRANSFER",
          "service_id" : 547,
          "oneload_transaction_type_id" : 2,
          "status" : "SUCCESS",
          "datetime" : "2021-08-24T10:16:09.000Z"
        }
      }

How should I search or delete records using datetime search so I can do further operations on it?

[spinscale (Alexander Reelsen)]

You can use the explain API to figure out what the date was rounded to... as usual a full reproduction would help here a lot... try this snippet as an example

# not in result set
PUT test/_doc/1
{
  "@timestamp" : "2021-08-22T12:34:56.789Z"
}

GET test/_mapping

GET test/_search
{
  "query": {
    "range": {
      "@timestamp": {
        "lt": "now-3d/d"
      }
    }
  }
}

GET test/_explain/1
{
  "query": {
    "range": {
      "@timestamp": {
        "lt": "now-3d/d"
      }
    }
  }
}

[rehannali (Rehan Ali)]

Hi @spinscale , Thanks for your swift response. As I have mentioned all payload which is available on elasticsearch index. I have datetime field which has a date type. If I only use now it worked but not with now-3d/d.

I have mentioned below my mapping. I have record of the last 7 days and still, it returns nothing.

{
  "my-index" : {
    "mappings" : {
      "properties" : {
        "datetime" : {
          "type" : "date"
        },
      }
    }
  }
}