MongoDB ISODate field search using Java

Question

I am having trouble searching ISODate field in mongodb using Java. I want to find exact matched date.

Here's how I query first collection and get ISODate field "Timestamp". Once I get this date, I want to search another collection with the same "Timestamp" value.

    FindIterable<Document> docList = thermalComfortCollection.find();
    for(Document doc: docList) {

        String ts = doc.get("Timestamp").toString();
        System.out.println(ts);

...

I am formatting ISODate since it returns me date in a different format that I would like to search. Therefore, I am converting it into this pattern "yyyy-MM-dd HH:mm:ss"

        final DateTimeFormatter inputFormat = 
                DateTimeFormatter.ofPattern("EEE MMM dd HH:mm:ss zzz yyyy");

        // The parsed date
        final ZonedDateTime parsed = ZonedDateTime.parse(ts, inputFormat);

        // The output format
        final DateTimeFormatter outputFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String date = outputFormat.format(parsed);

I could not find how to write exact match statement for ISODate type, so I am putting both gte and lte conditions to get an exact match..!

        BasicDBObject query = new BasicDBObject("Timestamp", //
                          new BasicDBObject("$gte", date).append("$lte", date));
                System.out.println(query);

And the query is not working. Could you give me any comment which part of my code is wrong?

Answer 1

Since MongoDB saves all dates in UTC with the timezone, you'll need to query on a Date instance with timezone set as UTC.

Let's day this is my document in MongoDB

{ "_id" : ObjectId("58886fa477717752e6eff16b"), "dd" : ISODate("2017-01-25T09:28:04.041Z") }

To query it from Java, I'll do:

String dateStr = "2017-01-25 09:28:04.041 UTC";
Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS ZZZ").parse(dateStr);
BasicDBObject filter = new BasicDBObject("dd", date);
coll.find(filter);