CODEWITHSUNDEEP
hibernateelasticsearchhibernate-search
Ashley Callaghan
Ashley Callaghan

Reputation: 105

Date range query not returning any results hibernate search with elasticsearch

Hibernate search is not returning any results when we use date range queries on our indexed dates.

All other range queries are working as expected

We are using hibernate search on top of elasticsearch all our queries run as expected apart from one where we try to find dates within a given range.

We have other range queries on weight, height etc which work as expected.

Examining the index with Luke the dates appear to be indexed as expected.

We have the following domain objects just showing relevant fields

@Indexed
public class Person{

      @IndexedEmbedded
      @OneToMany(mappedBy = "person", fetch = FetchType.LAZY)
      @Cascade({org.hibernate.annotations.CascadeType.PERSIST})
      private Set<DateOfBirth> datesOfBirth = new HashSet<>();
}

public class DateOfBirth{

     @Basic
     @Field
     @DateBridge(resolution = Resolution.MILLISECOND)
     @Column(name = "date_of_birth")
     private Date dateOfBirth;
}

I have also tried the following on the dateOfBirth field

@Basic
@Field(bridge = @FieldBridge( impl = ElasticsearchDateBridge.class))
@Column(name = "date_of_birth")
@JsonFormat(pattern = "dd/MM/yyyy")
private Date dateOfBirth;

We have the following code to add to our query which is meant to find all people within to ages passed from the UI

public void withAgeRange(Integer lowerAge, Integer upperAge){

    if(lowerAge != null || upperAge != null){

        LocalDateTime localDateTime = LocalDateTime.now();
        LocalDateTime lowerLocalDateTime = localDateTime.withYear(localDateTime.getYear() - upperAge);
        LocalDateTime upperLocalDateTime = localDateTime.withYear(localDateTime.getYear() - lowerAge);

        Date lowerDate = Date.from(lowerLocalDateTime.atZone(ZoneId.systemDefault()).toInstant());
        Date upperDate = Date.from(upperLocalDateTime.atZone(ZoneId.systemDefault()).toInstant());

        bool.must(getQueryBuilder().range().onField("datesOfBirth.dateOfBirth").from(lowerDate).to(upperDate).createQuery());
    }
}

Examining the query before it is executed shows

+dateOfBirths.dateOfBirth:[33590595604 TO 1106595795604]

Looking at an example document with Luke there is person with a date of birth with an indexed value of 320976000000 which falls between the lower and upper bound of the range.

Relevant section of Person mapping from Kibana

      "datesOfBirth": {
        "properties": {
          "currentDateOfBirth": {
            "type": "boolean"
          },
          "dateOfBirth": {
            "type": "date",
            "store": true
          }
        }
      },

As mentioned previously all our other range queries work as expected.

We would expect at least this one person to be returned but no matter what range we try we always get back 0 results.

Any ideas would be greatly appreciated.

Upvotes: 1

Views: 980

Answers (2)

Ashley Callaghan
Ashley Callaghan

Reputation: 105

Turns out if we annotate with

@Basic
@Field(bridge = @FieldBridge( impl = ElasticsearchDateBridge.class))
@Column(name = "date_of_birth")
@JsonFormat(pattern = "dd/MM/yyyy")  
private Date dateOfBirth;

And change the query to 

      bool.must(getQueryBuilder().range().onField("datesOfBirth.dateOfBirth").ignoreFieldBridge().from(dateFormat.format(lowerDate)).to(dateFormat.format(upperDate)).createQuery());

Where dateFormat is

 DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

It works as expected.

Not sure if this is the best approach though. Or is there a better way of doing it?

Seems kinda messy.

Upvotes: 0

ryanlutgen
ryanlutgen

Reputation: 3051

Looks like this might be an issue with timezones...

Looks like you are storing data with a java.util.Date. In my experience, this does not pass timezone information to Elasticsearch. Elasticsearch stores dates internally using UTC. If a timezone is passed, ES will convert it to UTC when it actually stores it. If no timezone is passed, ES will assume that your date is UTC, as is.

What this means is: Assuming you are running on the US WEST timezone (GMT-8), that means ES will think you meant for your date to be 8 hours before what you passed it in to be. For instance, you pass in a birth date of '01/25/19:12:00:00', ES will treat that as such in UTC, which is actually '01/25/19:4:00:00' your time. Meaning, any queries you try to run need to be adjusted by -8 hours with the way the documents are being indexed at the moment. Judging from your code, you are adjusting the queries to account for your timezone, but might not be when actually indexing.

Why all of your other queries work as expected, but not this one, I am unsure, but my hunch is the above.

To get around date issues, I index documents using ZonedDateTime at UTC.

For reference, here is a question, and subsequent answer once I figured it out, that I posted, when I was dealing with similar query issues: Query to pull with timestamps that were updated more than 30m ago not working

Upvotes: 1

Related Questions