AWS DocumentDB and Java: Query for records in a range

Question

I've never touched MongoDB before this week. I'm trying to query a collection that includes a date record. I want do a query based on a date range. I can query the entire collection, but my various attempts to filter my query have all failed.

I'm going to show what is working and what isn't.

package com.example.mongodb.service;

import com.mongodb.BasicDBObject;
import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import jakarta.annotation.PostConstruct;
import org.bson.Document;
import org.bson.conversions.Bson;
import org.springframework.stereotype.Service;

import java.util.Date;

/**
 * Do Things with DocumentDB.
 */
public class DocDBService {
    private String collectionName = "myCollection";

    private MongoClient mongoClient;
    private MongoDatabase database;

    private String username;
    private String password;
    private String server;
    private String dbName;

    /**
     * Construct with our username, password, and server.
     */
    public DocDBService(String usernameIn, String passwordIn, String serverIn, String dbNameIn) {
        username = usernameIn;
        password = passwordIn;
        server = serverIn;
        dbName = dbNameIn;

        String template = "mongodb://%s:%s@%s";
        String connectionString = String.format(template, username, password, server);
        mongoClient = MongoClients.create(connectionString);
        database = mongoClient.getDatabase(dbName);
    }

    /** This is working. */
    public long getLength() {
        BasicDBObject obj;

        return database.getCollection(collectionName).countDocuments();
    }

    /* I've tried a few things. None are working. */
    public long getLength(Date earliest, Date latest) {
        /*
        BasicDBObject query = new BasicDBObject();
        if (earliest != null) {
            query.put("date", new BasicDBObject("$gt", earliest));
        }
        if (latest != null) {
            query.put("date", new BasicDBObject("$lt", latest));
        }

        System.out.println(query.toJson());

        MongoCursor<?> cursor = database.getCollection(collectionName).find(query).iterator();
        long rv = 0;
        while (cursor.hasNext()) {
            ++rv;
            cursor.next();
        }
         */
        long rv = 0;
        Document filter = new Document();
        if (earliest != null) {
            filter.append("$gte", earliest);
        }
        if (latest != null) {
            filter.append("$lte", latest);
        }
        System.out.println(filter.toJson());
        rv = database.getCollection(collectionName).countDocuments(new Document("date", filter));
        return rv;
    }
}

I've been calling the date-based query with start date and/or an end date (the 3 possible combinations). In all cases, I get back a count of 0.

With two dates, dumping the filter shows:

{"$gte": {"$date": "2023-10-01T05:00:00Z"}, "$lte": {"$date": "2023-11-02T05:00:00Z"}}

I wonder if the format of the dates is the problem, as when I display the collection (using DataGrid), they are dates, not timestamps. That is, 2023-10-01 not 2023-10-01 05:00:00.

Answer 1

And yes, it was the format of the dates. This works with a single date but not with a date range:

    public long getLength_ByString(Date earliest) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        BasicDBObject query = new BasicDBObject();

        query.put("date", new BasicDBObject("$gt", formatter.format(earliest)));

        System.out.println(query.toJson());

        MongoCursor<?> cursor = database.getCollection(collectionName).find(query).iterator();
        long rv = 0;
        while (cursor.hasNext()) {
            ++rv;
            cursor.next();
        }
        return rv;
    }

This method also works and supports a range:

    public long getLength_ByFilter(Date earliest, Date latest) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        long rv = 0;
        Document filter = new Document();
        if (earliest != null) {
            filter.append("$gte", formatter.format(earliest));
        }
        if (latest != null) {
            filter.append("$lte", formatter.format(latest));
        }
        System.out.println(filter.toJson());
        rv = database.getCollection(collectionName).countDocuments(new Document("date", filter));

        return rv;
    }

I've spent several hours on this, and it came down to the date format. Sigh.