Java Range Aggregation over Unix timestamps

Question

In our elasticsearch (version 2.3.1) index we have a showDate field for every document. This field contains a unix timestamp and I want to find out how many documents have a showDate in the last 24 hours and how many documents have a showDate in the last 7 days.

In my Java code this is how I tried to accomplish this:

public static final String TODAY = "today";
public static final String THIS_WEEK = "thisWeek";
protected static final int SECONDS_PER_DAY = (24 * 60 * 60);

//...

Date now = new Date();
double timestampNow = now.getTime() / 1000;
double timestampYesterday = timestampNow - SECONDS_PER_DAY;
double timestampThisWeek = timestampNow - (SECONDS_PER_DAY * 7);
AbstractAggregationBuilder aggregation = AggregationBuilders.range("time").field("showDate")
                .addRange(TODAY, timestampYesterday, timestampNow)
                .addRange(THIS_WEEK, timestampThisWeek, timestampNow);
searchRequestBuilder.addAggregation(aggregation);

When I execute this search request and look at the produced query I get something like this

{
  "from": 0,
  "size": 15,
  "query": {
      ...
  }
  "aggregations": {
    "time": {
      "range": {
        "field": "showDate",
        "ranges": [
          {
            "key": "today",
            "from": 1.465369290E9,
            "to": 1.465455690E9
          },
          {
            "key": "thisWeek",
            "from": 1.464850890E9,
            "to": 1.465455690E9
          }
        ]
      }
    }
  }
}

When I execute the query in a plugin such as Kopf or Head I get the expected result. When I execute it in my java service the docCount is always 0.

The only difference I noticed is that the plugins convert the double value into a long value. So after I sent the query the 1.465455690E9 numbers are converted to 1465455690. Does anyone know how I get the same result in my Java service as I get from the plugins?

Update 1 Thanks to Dimitris' answer my code looks like this. However it still doesn't give the the expected results. The DocCount is still always 0 even though Head and Kopf tell my there are results.

    Date now = new Date();
    long timestampNow = now.getTime() / 1000;
    long timestampYesterday = timestampNow - SECONDS_PER_DAY;
    long timestampThisWeek = timestampNow - (SECONDS_PER_DAY * 7);
    AbstractAggregationBuilder aggregation = AggregationBuilders.dateRange(KEY).field("showDate").format("epoch_second")
            .addRange(TODAY, timestampYesterday, timestampNow)
            .addRange(THIS_WEEK, timestampThisWeek, timestampNow);

Update 2: My final solution looks like this:

Date now = new Date();
long timestampNow = now.getTime() / 1000;
long timestampYesterday = timestampNow - SECONDS_PER_DAY;
long timestampThisWeek = timestampNow - (SECONDS_PER_DAY * 7);
AbstractAggregationBuilder aggregation = AggregationBuilders.dateRange(KEY).field("showDate").format("epoch_second")
            .addRange(TODAY, String.valueOf(timestampYesterday), String.valueOf(timestampNow))
            .addRange(THIS_WEEK, String.valueOf(timestampThisWeek), String.valueOf(timestampNow))

Also I had to change my index so that showDate is of type date (the format is still epoch_second).

Dimitris Athanasiou · Accepted Answer · 2016-06-09 13:34:22Z

1

Use AggregationBuilders.dateRange instead.

In your example:

...
AbstractAggregationBuilder aggregation = AggregationBuilders.dateRange("time").field("showData").format("epoch_second")
                                                            .addRange(TODAY, (long) timestampYesterday, (long) timestampNow)
                                                            .addRange(THIS_WEEK, (long) timestampThisWeek, (long) timestampNow);

Note that you need to set the format in which the dates are set, in your case "epoch_second" for the corresponding elasticsearch built-in format (see: https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html#built-in-date-formats)

Update

It turns out the timestamps have to be passed in as Strings. Thus the code becomes:

...
AbstractAggregationBuilder aggregation = AggregationBuilders.dateRange("time").field("showData").format("epoch_second")
                                                            .addRange(TODAY, String.valueOf(timestampYesterday), String.valueOf(timestampNow))
                                                            .addRange(THIS_WEEK, String.valueOf(timestampThisWeek), String.valueOf(timestampNow));

edited Jun 9, 2016 at 13:34

answered Jun 9, 2016 at 10:59

Dimitris Athanasiou

664 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

mvieghofer Over a year ago

Thanks for your answer. I updated my code (and also posted it in my question above) and at least I get a query that looks more like what I expect. Sadly the docCount is still 0... Do you have any idea why?

Dimitris Athanasiou Over a year ago

Have you tried manually constructing the search and posting it directly to your cluster? Is "showData" mapped as a date field?

mvieghofer Over a year ago

I write the searchRequestBuilder to the console using searchRequestBuilder.toString(). This contains the query that is sent to the cluster. When I copy this query and send it to the cluster using Head or I get the expected result (docCount != 0). When I execute the query in my Java service the docCount is 0.

Dimitris Athanasiou Over a year ago

Could you please try again but this time pass the timestamps as epoch milliseconds and set the format to "epoch_millis"?

Dimitris Athanasiou Over a year ago

Actually strike that. I know what the problem is. You have to pass the timestamps in the ranges as Strings.

|

Collectives™ on Stack Overflow

Java Range Aggregation over Unix timestamps

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related