Filter by time with any date

Question

I want to filter documents only by time. For example, if I provide timestamps like: 7:00:00 and 15:00:00 by GMT, they should return dates like 15 Dec 2020, 30 April 1340 and so on.

This query obviously does not work (I use node.js client), because it returns any date between 1000 year and 3000 year:

searchResult = await esClient.transport.request({
  method: 'GET',
  path: 'events/_search',
  body: {
    query: {
      bool: {
        must: {
           range: {
             date: {
               gte: '1000-07-15T07:00:00Z',
               lte: '3000-07-15T15:00:00Z'
             }
           }
         }
      }
    },
  }
});

As well as this query, because, according to this docs, my provided timestamps (7:00:00 and 15:00:00) are converted into dates with default values:

searchResult = await esClient.transport.request({
  method: 'GET',
  path: 'events/_search',
  body: {
    query: {
      bool: {
        must: {
           range: {
             date: {
               'gte': '7:00:00',
               'lte': '15:00:00',
                format: 'HH:mm:ss'
             }
           }
         }
      }
    },
  }
});

How can I filter only by time, but not days, months, years?

score 2 · Accepted Answer · answered Dec 14 '20 at 10:48

Adding to the "Missing date components" part from the docs -- if there's no YYYY-MM-DD, it'll default to 1970-01-01.

What's usually done when you want to query date-less time ranges is that you save the elapsed time since midnight of that day in a purely numerical value. Depending on the precision needs of the use case it could be

the hour (as in the business opening hours problem, for example)
seconds since midnight
etc.

That way you're left with a simple numerical range query. The incoming ranges would then of course need to be provided in a format compliant with the way you're storing the time values.

score 1 · Answer 2 · answered Dec 14 '20 at 10:53

I managed to do it by storing two separate fields: date strict_date and time strict_hour_minute_second:

searchResult = await esClient.transport.request({
  method: 'GET',
  path: 'events/_search',
  body: {
    query: {
        range: {
          time: {
            gte: '14:20:00',
            lte: '14:30:00'
          }
        }
      }
  }
});

Filter by time with any date

2 Answers2

Linked