# Help me to refine this query **URL:** \ **Category:** Logstash\ **Tags:** painless\ **Created:** [August 6, 2020, 12:48pm UTC](https://discuss.elastic.co/t/help-me-to-refine-this-query/244023 "2020-08-06T12:48:01Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [August 6, 2020, 12:48pm UTC](https://discuss.elastic.co/t/help-me-to-refine-this-query/244023/1 "2020-08-06T12:48:01Z")
Context: - Logstash Elasticsearch Input plugin does not maintain a state like JDBC input plugin. - However it does provide scheduling support. - So I decided to pull data daily from the previous day records, e.g. from say 4th Aug 00:00:00:000 to 5th Aug 00:00:00:000. If there is anyother way please do share. Here is the script I came up with. Somehow I feel there should be a better way of doing it. **Also the CPU on the nodes shoots up high when I run this.** The idea is to get current date and set the time to 00:00:00:000 and then get the date before it. Then use these two dates to get all the events falling between them. ``` GET testindex/_search { "query": { "bool": { "must": [ { "script": { "script": { "source": """ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Calendar start = Calendar.getInstance(); Calendar end = Calendar.getInstance(); end.set(Calendar.HOUR_OF_DAY, 0); end.set(Calendar.MINUTE, 0); end.set(Calendar.SECOND, 0); start.add(Calendar.HOUR, -1*24); start.set(Calendar.HOUR_OF_DAY, 0); start.set(Calendar.MINUTE, 0); start.set(Calendar.SECOND, 0); def startTime = start.getTimeInMillis(); def endTime = end.getTimeInMillis(); def eventTime = doc["@timestamp"].date.toInstant().toEpochMilli(); return eventTime > startTime && eventTime <= endTime;""" } } }, { "match": { "message": "This is expensive to run !!" } } ] } }, "sort": [ { "@timestamp": { "order": "asc" } } ] } ``` As of now it works but I am sure there is a more native way of doing this in Elasticsearch painless script. Maybe some in built DateTime/Java.Time way rather than the Java.Calendar. EDIT: Changed tags to Logstash ---
**Author:** ![Jenni](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jenni/32/29684_2.png) [@Jenni](https://discuss.elastic.co/u/Jenni)\ **Post date:** [August 7, 2020, 1:49am UTC](https://discuss.elastic.co/t/help-me-to-refine-this-query/244023/2 "2020-08-07T01:49:11Z")
Am I misunderstanding something or isn't that just a very laborious way to implement a date range as a script? And even the exact same query as the one in the documentation? [https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#ranges-on-dates](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#ranges-on-dates) ```auto "range": { "@timestamp": { "gte": "now-1d/d", "lt": "now/d" } } ``` (Edit: Well. To be precise, your query would be this with `gt` and `lte` instead :)) ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [August 7, 2020, 2:52am UTC](https://discuss.elastic.co/t/help-me-to-refine-this-query/244023/3 "2020-08-07T02:52:45Z")
It is critical that I do not lose any events at the start and the end of the timeframe (I am using elaspsed time plugin of logstash). I am not very confident if running the job at daily schedule at say 12 in night will get all the samples at the edges. The `now` might (will?) have different values on the three nodes I have by the time it starts execution. I did not find the documentation stating that `now` value will be fixed at the issue of the command. Hence decided to see if I can provide a hard range of (Day-1) 00:00:000 to (Day) 00:00:000. Happy to be proven wrong and super happy to use the now-1d/d based ranges. It will make life super easy. BTW I did try this also as one of the suggestions but it did not work: ``` "range": { "@timestamp": { "gte": "now-1d/d", "lt": "now/d", "format": "yyyy-MM-dd" } } ``` But `now` still had the time part. Is there any way to round off `now` to nearest hour? That can also work for me. ---
**Author:** ![Jenni](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jenni/32/29684_2.png) [@Jenni](https://discuss.elastic.co/u/Jenni)\ **Post date:** [August 7, 2020, 3:29am UTC](https://discuss.elastic.co/t/help-me-to-refine-this-query/244023/4 "2020-08-07T03:29:49Z")
> [@pk.241011](#): > > but it did not work: What did it do? This should give you the documents between midnight of the day before and midnight today. (I don't know why the format parameter is there when only relative times are used in the query, so the format isn't used anywhere?) > [@pk.241011](#): > > But `now` still had the time part What do you mean? > [@pk.241011](#): > > Is there any way to round off `now` to nearest hour You could use `/h` instead of `/d` to round down to the hour instead of the day. That is explained in the [date math](https://www.elastic.co/guide/en/elasticsearch/reference/current/common-options.html#date-math) documentation that is linked in the article mentioned above. * * * Edit: As a side note: You could have mentioned that you've already got a topic for this. Didn't setting the `time_zone` help? > [@How to fix this date range](https://discuss.elastic.co/t/how-to-fix-this-date-range/243065): > > Hi, Here is sample code from the [documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#ranges-on-dates): GET /\_search { "query": { "range": { "timestamp": { "gte": "now-1d/d", "lt": "now/d" } } } } I want to change it so that now only uses the date part and not the time. My usecase is to get previous day data for 24 hours e.g Between 00:00:00 and 23:59:59. In other words the range should be [28-07-2020 to 29-07-2020]. Is there any extension on now like now.Date? I was looking at painless script but not su… ---
**Author:** ![pk.241011](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/pk.241011/32/86285_2.png) [@pk.241011](https://discuss.elastic.co/u/pk.241011)\ **Post date:** [August 10, 2020, 1:37pm UTC](https://discuss.elastic.co/t/help-me-to-refine-this-query/244023/5 "2020-08-10T13:37:21Z")
Hi @Jenni, I did have that topic but it did not help. I think I should have provided an closure on that. Apologies for that. My understanding of `now` was flawed. 😞 Re-reading the document sorted everything. 🙂 I went for hourly collection of data instead of daily. ``` "range": { "timestamp": { "gte": "now-1h/h", "lt": "now/h" } } ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [September 7, 2020, 1:37pm UTC](https://discuss.elastic.co/t/help-me-to-refine-this-query/244023/6 "2020-09-07T13:37:36Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.