Not able to filter time correctly in ElasticSearch query

Question

The records which are saved in ES have a field called updated_at which stores time in epoch. In my search query I'm filtering date based on 'yyyy-mm-dd' format as follows:

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "query": {
              "bool": {
                "filter": [
                  {
                    "range": {
                      "segment_status.updated_at": {
                        "from": "2022-05-16",
                        "to": "2022-05-16",
                        "include_lower": true,
                        "include_upper": true,
                        "boost": 1
                      }
                    }
                  }
                ],
                "adjust_pure_negative": true,
                "boost": 1
              }
            },
            "path": "segment_status",
            "ignore_unmapped": false,
            "score_mode": "avg",
            "boost": 1
          }
        }
        ]
    }
  }
        
}

My time zone is GMT+05:30. I have a record saved with epoch time 1652733000 which comes out to be 17th May at around 2:00 am. Using the above ES query I'm hitting this record and I'm assuming that is because the time at GMT is 16th May.

Please validate this and it would be helpful if there is a walkaround to fix this in the code generating the query.

Answer 1

You are correct, those miliseconds since epoch evaluate Monday, May 16, 2022 8:30:00 PM UTC time. If you want to enable users to search by date in their own timezones, you have to stop thinking in terms of your own timezone. If you have access to the date as LocalDate object and the timezone of the user, you can make queries against elasticsearch using miliseconds since epoch:

LocalDate date = ...
Instant startOfDay = date.atStartOfDay(userZoneId).toInstant();
Instant endOfDay = startOfDay.plusMillis(3600 * 24 * 1000 - 1);