Search for an exact Date, ignoring the time_zone

[wil93 (Wim Lambrecht)]

Given the following document:

PUT /examples/_doc/1
{ 
  "item": "Phone X",
  "price": 799,
  "lastUpdateDate" : "2023-05-01T01:00:00+02:00"
}

No explicit 'Mapping' for index 'examples' is provided (Elasticsearch will take care of the data types here).
The 'lastUpdateDate' is in the ISO6801 format as expected. And that given Date will be analyzed and be indexed as a Date type.
And I do know that the date will internally be converted to its UTC equivalent (and then stored as a 'long since the Epoch'. Which in this example will be:
"2023-04-30T23:00:00Z" - Epoch 1682895600

The Question:
I want to search for every document that had a last update on May 1st 2023 - in other words: 'the exact date, ignoring the time-zone'.

GET /examples/_search
{
  "query": {
    "range": {
      "lastUpdateDate": {
        "gte": "2023-05-01",
        "lte": "2023-05-01"
      }
    }
  }
}

I do know that those dates in the query will also be converted to UTC (and the time 00:00:00 will be used etc). That means that my doc '1' will not/never be found, since my lastUpdatedDate in the inverted index is April 30th 2023 (because of the UTC conversion).
I also don't want to provide a "time_zone" field in my Range query, because the time-zone in my query is irrelevant ... I just want to see all docs that were updated on "2023-05-01" ... (whatever the timezone I'm in now).
Any ideas or suggestions?
Thanks in advance !
Kr,
-wil-

[warkolm (Mark Walkom)]

Welcome to our community!

Your query is looking for documents with a lastUpdateDate greater than 2023-05-01 and less than 2023-05-01, that is going to return everything but that particular date.

I think you want;

GET /examples/_search
{
  "query": {
    "range": {
      "lastUpdateDate": {
        "gte": "2023-04-31",
        "lte": "2023-05-02"
      }
    }
  }
}

However;

@wil93

I also don't want to provide a "time_zone" field in my Range query, because the time-zone in my query is irrelevant

If you don't provide a timezone then Elasticsearch will assume you are talking UTC and will return results based on that.

@wil93

I just want to see all docs that were updated on "2023-05-01" ... (whatever the timezone I'm in now).

Then you need to provide the TZ to Elasticsearch in the query.

There's no magic option here sorry.

[wil93 (Wim Lambrecht)]

Hello Mark,
Thanks for your reply.

Regarding your first reply:

"gte": "2023-04-31",
"lte": "2023-05-02"

I do know that if you don't provide a TZ, ES will assume UTC. And that's part of my whole issue: I want to explicitly search on "2023-05-01", a TZ should/must be irrelevant in my search.

Your search will also return hits with 'lastUpdatedDate'-s on 2023-04-30, which I don't want.
BTW: "2023-04-31" is not a real date ... only 30 days in april Unless you deliberately did this to avoid "2023-04-30" hits ...

Regarding your second reply:
"Then you need to provide the TZ to Elasticsearch in the query."
The same thing as before here; I want to explicitly search on "2023-05-01", a TZ should/must be irrelevant in my search (=ignoring). The TZ extra field in a Range query is the TZ relative to our current time (now) and even more: we don't know when the doc with that 'lastUpdateDate' (and it's TZ) was entered upfront (because we are searching for it).

Known solution 1: we could extract the date time as a String and index it as a String in an extra field ... but then we loose the Date Range query power ...

Known solution 2: we could extract the date time, and replace the '+02:00' TZ part with a 'Z' (effectivily making it (a) UTC ourselves) and index it in an extra field (this will be our so-called 'hard local time').
That way we could still use the Date Range query power.

Bot solutions mean 1 extra field per data type field we want to be searcheable in that wy.
All of this seems a bit odd to mean me in the spirit of Elastic ...

[Christian_Dahlqvist]

@wil93

I do know that if you don't provide a TZ, ES will assume UTC. And that's part of my whole issue: I want to explicitly search on "2023-05-01", a TZ should/must be irrelevant in my search.

If you want to search on the exact date string that was submitted without considering time zone I believe you need to index the date string part as a keyword field, possibly using a multifield.

[warkolm (Mark Walkom)]

I would use Christian's ideal to be honest, that will get you what you want without using date searches and dealing with timezones.

@wil93

a TZ should/must be irrelevant in my search.

The potential time window on a date that is 2023-05-01, or any other specific single digit day, is actually 50 hours. That doesn't make a tonne of sense if you are at one end of that range and you are seeing data from what is 2 days ago.

[wil93 (Wim Lambrecht)]

@Christian_Dahlqvist & @warkolm

Ok guys, thanks for your reply.

Multi-map the date or copy it to an extra field as a pure String (+keyword mapping) was already a potential solution for us. But as I said, we loose the power of Date type searches, Date match, etc.
We already do this for a different use-case actually.

I do think of some other solutions: provide an extra field mapping using the original incoming date-time (delivered as a String). A constraint (but that's okay) is that the incoming date-time should be in this form then (ISO 8601 using an explicit TZ). For instance:
2023-05-01T01:02:03.004+02:00

We could then remove the TZ part and synthetically and deliberately make it Zulu/UTC, for this example resulting in:
2023-05-01T01:02:03.004Z
-> then store this in an extra field: "{original_name}_MY_UTC" (<- just a name ...).

That way we can query the MY_UTC variants and still use Date capabilities.
Just an idea ...