How do you find the closest document to a specific date?

[Cabaal]

Problem: I cannot find a document with a date greater than or date less than a given date. Spring Mongodb query's always return the same metric (Seemingly ignoring my sort order).

I have the following entity:

public class Metric {

    @NotNull
    private String name;

    @NotNull
    private double x;

    @NotNull
    private double y;

    @NotNull
    private double sd;

    @NotNull
    private double el;
    
    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(iso=ISO.DATE_TIME)
    private Date date;
}

And the following method in my service used to search for a particular metric less than a particular date:

public Metric findByNameAndDateLessThan(String name, Date date){
        Query q = query(where("name").is(name).and("date").lt(date));
        q.sort().on("date", Order.DESCENDING);
        return mongoTemplate.findOne(q, Metric.class, "metric");
    }

I need the CLOSEST, which I thought sorting would handle. In PHP I used "TOP 1" after sorting based on date and it worked fine.

I wrote a test as follows:

    @Test 
    public void testFindByNameAndDateLessThan(){
        Metric m1 = mdod.getRandomMetric();
        m1.setName("FindByNameAndLessThan");
        m1.setDate(new Date(20000));
        mr.save(m1);
        
        Metric m2 = mdod.getRandomMetric();
        m2.setName("FindByNameAndLessThan");
        m2.setDate(new Date(22000));
        mr.save(m2);
        
        Metric result = ms.findByNameAndDateLessThan(m1.getName(), new Date(22100));
        Assert.notNull(result, "Result must not be null. Did not find the latest metric");
        Assert.isTrue(m2.getDate().compareTo(result.getDate()) == 0, "Result Metric is incorrect. Found date '" + result.getDate().toGMTString() +"' instead");
        
        mr.delete(m1);
        mr.delete(m2);
    }

The RESULT is always the earliest metric, even though I am sorting. It seems Spring Mongo is returning all the results and ignoring the sort order.

Is there something wrong with my timestamp? Or the format? I thought IDO Date was correct, but when Spring Roo starts it tells me "It is recommended to use @DateTimeFormat(style="M-") on java.util.Date.joinDate to use automatic date conversion in Spring MVC"

Thoughts? I don't want this to be show stopper and am on a tight deadline. Thank you.

[Cabaal]

The query that ends up being built from the is the following:

{ "name" : "FindByNameAndLessThan" , "date" : { "$lt" : { "$date" : "1970-01-01T00:00:22.100Z"}}}

Which, if I run it in mongo shell, does not return any results. I am looking further into this.

[Cabaal]

Of course the above does not work in the shell, but it works if I modify it to:

{ "name" : "5d7E" , "date" : { "$lt" : ISODate("1970-01-01T00:00:22.10Z")}}

More reading has revealed that the resultant query from java will not work in the Mongo Shell. There is useful info here:

http://groups.google.com/group/mongo...5ba954d0e2440d

But not a complete answer.

[Cabaal]

Spring MongoDb was of course doing exactly what it was supposed to do. The answer for obtaining the single latest metric based on a date is:

Query q2 = new Query();
q2.sort().on("date", Order.DESCENDING);
q2.addCriteria(where("name").is(name).and("date").lte(date));
q2.limit(1);

The limit is important because it cuts down on the cost of retrieving more results than necessary.

The sort will NOT work on methods that find a single result, such as findOne. In SQL this works, but not in Mongo. After all, why sort when you only want one result?

I hope this helps someone else.