Issue with Range query using hour_minute format

[Daniel_Scott]

I'm having issue using the range query with the various time formats. Here is my query in question:

{
	"_source": ["created_on"],
	"fields": [{
		"field": "created_on",
		"format": "hour_minute"
	}],
	"query": {
		"range": {
			"created_on": {
				"gt": "23:59",
				"format": "hour_minute"
			}
		}
	},
	"sort": [{
		"created_on": "desc"
	}],
	"size": 25,
	"from": 0,
	"track_total_hits": true
}

I'll get a hit like
<>

{
        "_source": {
          "created_on": "2023-06-08T17:43:29.364Z"
        },
        "fields": {
          "created_on": [
            "17:43"
          ]
        },
        "sort": [
          1686246209364
        ]
      }

So the gt part of the query is ignored. No matter what I put in for gt I get all results. Inversely if I put any value in for lt I get no results.

Any thoughts on this?

[dadoonet (David Pilato)]

Welcome!

What's the mapping of the field?

[Daniel_Scott]

Mapping of the field is date

 "created_on": {
          "type": "date"
        }

[dadoonet (David Pilato)]

If you don't care about the date itself but just the hour of the day, you need to index that value as a the hour of the day.
I'd recommend doing some maths here and for 17:43 add a field:

"mn_of_the_day": 1063

And then use that field.

To do this, you can either do that from your application which generates the data, or you can use an Ingest Script Processor.

If you can't do that before indexing, then you can do that with a runtime field (using a script) but that will be much slower. You have an example in the documentation which computes the day of the week. You can use that as a start.

[Daniel_Scott]

Interesting. Am I correct in assuming that format on a range query does not cast the value? That seems to be what the documentation indicates.

I originally was looking to do this with a painless script but was unable to find a way to pull full time from a date, I can pull hour or minute but not them together, unless I'm missing something