Query on a date field that contains only a time value

I have a full date field (epoch_second), but I also want to be able to query based on the just the time, so I made another date field ("time" with "hour_minute_second" format).

By default, Kibana shows this "time" field values as full dates with the epoch day, e.g. "1970-01-01T21:35:49.000Z". That's fine (since you can customize the display), but I can't find a way to query based on this field.

Just to start more simply, let's try to find "late night" events, say "20:00:00" to "23:59:59".

Here are some things that I've tried:

  • clicking the "Filter for value" icon next to the "time" column. This throws a "Bad Request" exception (even doing that on the main datetime field throws the same error).
  • querying for 'time > 1970-01-01T20:00:00.000Z'. This gives the error: Expected AND, OR, end of input, whitespace but ":" found
  • querying for 'time > "1970-01-01T20:00:00.000Z"', 'time > "20:00:00.000Z"' and pretty much anything that looks like a time throws a "Bad Request"

So, what's the secret sauce?

The real request would be for 10PM-6AM, which I imagine will require two queries (>10 OR <6). If there's another way, please let me know.

Hi, you can query on a timefield as for you do for other fields.
I've just tried a similar query on discover query input field and it worked:

@timestamp >= "2020-08-26T20:00:00.000Z" AND @timestamp <= "2020-08-26T23:59:59.999Z"

Could you please share the version of Kibana you are using and if possible the error logs from the bad request?
this request should work if you are looking for everything that is after that time.

time > "1970-01-01T20:00:00.000Z"

The only thing you can't do right now with these queries is to query only on that specific night range on every day. For that you can use a scripted field that compute the hour of day and use that field to filter out your hourly range

Marco, thanks for the reply. I was also confused when using the "+" tool to filter on either date field threw an error.

Using your query also throws an error:

Error loading data
Bad Request
search_phase_execution_exception
all shards failed

Error: Bad Request
    at Fetch._callee3$ (http://localhost:5601/33813/bundles/core/core.entry.js:34:109213)
    at l (http://localhost:5601/33813/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:368:155323)
    at Generator._invoke (http://localhost:5601/33813/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:368:155076)
    at Generator.forEach.e.<computed> [as next] (http://localhost:5601/33813/bundles/kbn-ui-shared-deps/kbn-ui-shared-deps.js:368:155680)
    at fetch_asyncGeneratorStep (http://localhost:5601/33813/bundles/core/core.entry.js:34:102354)
    at _next (http://localhost:5601/33813/bundles/core/core.entry.js:34:102670)

This is a small playground running under windows. Nothing shows in the windows from which I ran ES or Kibana. I haven't had trouble with querying any other fields (except these time fields).

I don't see a log file in the kibana directory, and there are no errors in the elasticsearch.log file.

Marco, any new ideas, or any more info I can provide to help track this down?

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.