ElasticSearch : Filter data by hour and minute on a timestamp field

Question

I have created an index with "TimeStamp" field which has date and time data in standard ISO format "YYYY-MM-DD HH:MM:SS+00:00". I have a requirement of filtering data that falls between specific time on a given day. For example, I want to filter data that was collected between 6am and 12:30 pm on all days of January month of 2019. I used the script tag to filter by hours and it works. But I am not able to figure out how to add minutes to the equation. Here is what my query currently looks like

{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "TimeStamp": {
                  "gte": "2019-01-01T00:00:00.000Z",
                  "lte": "2019-01-31T23:59:59.000Z"
                }
              }
            },
            {
              "script": {
                "script": {
                   "source": "(doc['TimeStamp'].value.getHour() >= 6 && doc['TimeStamp'].value.getHour() <= 22)"
                }
              }
            }
          ]
        }
      }
    }
  }
}

This gives me data for specific hours, but how do I add minutes to this? Adding getMinute() actually applies it separately and filters out relevant results. Could anyone please guide me with this?

Query would be more efficient if you can store date and time in separate fields as well. — Nishant, Apr 08 '19 at 09:52
This answer might help: https://stackoverflow.com/a/34939653/4604579 — Val, Apr 08 '19 at 11:13
@NishantSaini You mean create a new filed with just time? But the data is already indexed and adding a new index may not be feasible. — Cognoscis, Apr 09 '19 at 13:03

James Pittiglio · Accepted Answer · 2019-04-08T17:49:08.720

You're on the right track - as you indicated, the getMinute method is being applied in all cases. However, using this method of stripping out the hour and minute fields, you need to add an additional set of conditionals only when the hour is at the upper bound. Note if you want to also have an adjustable minute for the lower bound, additional conditionals would be needed, but this should get you started:

{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "TimeStamp": {
                  "gte": "2019-01-01T00:00:00.000Z",
                  "lte": "2019-01-31T23:59:59.000Z"
                }
              }
            },
            {
              "script": {
                "script": {
                  "params": {
                    "lowerBoundHour": 6,
                    "upperBoundHour": 12,
                    "upperBoundMin": 30
                  },
                  "source": """
if (doc['TimeStamp'].value.getHour() >= params.lowerBoundHour && doc['TimeStamp'].value.getHour() <= params.upperBoundHour) {
    if (doc['TimeStamp'].value.getHour() == params.upperBoundHour) {
        if (doc['TimeStamp'].value.getMinute() <= params.upperBoundMin) {
            return true
        } else {
            return false
        }
    } else {
        return true
    }
} else {
    return false
}
"""
                }
              }
            }
          ]
        }
      }
    }
  }
}

As this looks a little convoluted and may not be the most performant, I'd recommend also taking a look at using the LocalTime methods to convert the TimeStamp field, as it's likely this could be written more cleanly using a simpler conditional from there.

UPDATE:

I think this version looks a little cleaner:

{
  "query": {
    "bool": {
      "filter": {
        "bool": {
          "must": [
            {
              "range": {
                "TimeStamp": {
                  "gte": "2019-01-01T00:00:00.000Z",
                  "lte": "2019-01-31T23:59:59.000Z"
                }
              }
            },
            {
              "script": {
                "script": {
                  "params": {
                    "lowerBoundHour": 6,
                    "lowerBoundMin": 0,
                    "upperBoundHour": 12,
                    "upperBoundMin": 30
                  },
                  "source": """
def formattedTime = LocalTime.of(doc['TimeStamp'].value.getHour(), doc['TimeStamp'].value.getMinute());
def lowerBound = LocalTime.of(params.lowerBoundHour, params.lowerBoundMin).minusNanos(1000000);
def upperBound = LocalTime.of(params.upperBoundHour, params.upperBoundMin).plusNanos(1000000);

if (lowerBound.isBefore(formattedTime) && formattedTime.isBefore(upperBound)) {
    return true;
} else {
    return false;
}
"""
                }
              }
            }
          ]
        }
      }
    }
  }
}

Note the minusNanos and plusNanos are necessary to subtract or add a millisecond from your bounds - the isBefore (and related isAfter) methods of LocalTime are not inclusive. This will also allow you to easily make adjustments to the lower bounded time.

Thank you for this. Very helpful. I also needed additional filtering by multiple timeslots which I can do using the same format that you have provided. Thank you for pointing out these methods. — Cognoscis, Apr 09 '19 at 13:00

score 1 · Answer 2 · answered Oct 03 '19 at 15:09

I needed the same thing (filtering in Kibana based on the hour of the timestamp), but the script from James did not work for me, I needed to tweak it a bit (the syntax being doc['fieldname'].date.hourOfDay), so here is a version which worked for me:

{
    "query": {
        "bool": {
            "must": {
                "script": {
                    "script": {
                        "params": {
                            "lowerBoundHour": 6,
                            "upperBoundHour": 20
                        },
                        "source": "doc['@timestamp'].date.hourOfDay >= params.lowerBoundHour && doc['@timestamp'].date.hourOfDay <= params.upperBoundHour",
                        "lang": "painless"
                    }
                }
            }
        }
    }
}

ElasticSearch : Filter data by hour and minute on a timestamp field

2 Answers2