Question in elastic search to get specific records with specific date and time range

Hello all, I would like to perform a query to get last weeks records on our log which the record are within Mon-Fri from (19:00-07:00) and Sat from (15:00-00:00) and full day on Sunday. How should I write the query? Thanks.

Welcome to our community! :smiley:

Does https://www.elastic.co/guide/en/elasticsearch/reference/7.10/query-dsl-range-query.html#ranges-on-dates help? What have you tried so far?

I tried to get the records between 2021-01-07 to 2021-01-13 with the below commands but it fails.

GET /project.sli-app01.*/_search
{
  "query": {
    "bool": {
      "must": {
        "match": {
          "message": "successLoginID"
        }
      },
      "filter": {
        "bool": {
          "must": [
             {
              "range": {
                "@timestamp": {
                  "format": "YYYY-MM-DD",
                  "time_zone": "+08:00",
                  "gte":"2021-01-07",
                  "lt": "2021-01-13"
                }
              }},{
              "range": {
                "@timestamp": {
                  "format": "HH:mm",
                  "time_zone": "+08:00",
                  "gte":"09:00",
                  "lt": "18:00"
                }
              }
              }
          ]
        }
      }
    }
  },
  "size": 1000,
  "_source": [
    "@timestamp",
    "level",
    "message",
    "kubernetes.container_name"
  ]
}

Fails how?

No result found, but it is expected that there should have records.

Anyone has idea? Thanks.

Have you tried finding some of these documents in Discover in Kibana, using that same timeframe?

Yes and result can be found

Ok, if you click on Inspect in the top right, it'll show you the request it sent. Copy that and you should get results.