3

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?

roeygol
  • 4,908
  • 9
  • 51
  • 88
ejshin1
  • 1,107
  • 6
  • 17
  • 35

3 Answers3

5

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);
ares
  • 4,283
  • 6
  • 32
  • 63
  • Thank you! I found a problem in my code, it was because I did not correctly put filter with date. – ejshin1 Jan 25 '17 at 17:47
2

You are comparing the dates not their string formats.

For example, to compare on below timestamp.

{ "timestamp" : ISODate("2017-01-25T09:28:04.041Z") }

Code:

Instant instant = Instant.parse("2017-01-25T09:28:04.041Z"); //Pass your date.

Date timestamp = Date.from(instant);

Query:

Document query = Filters.eq("timestamp", timestamp);
s7vr
  • 73,656
  • 11
  • 106
  • 127
  • If I use this code, then it throws an error: _java.time.format.DateTimeParseException: Text 'Wed Dec 21 05:45:00 PST 2016' could not be parsed at index 0_ – ejshin1 Jan 25 '17 at 17:32
  • Are you taking date as a string input ? Looks like your datetime has time zone. Parse to `ZonedDateTime - Instant - Date` – s7vr Jan 25 '17 at 18:20
  • I initially put date as a string. It should be date structure, got it, thanks! – ejshin1 Jan 30 '17 at 06:14
0
Instant instant = Instant.parse("2017-01-25T09:28:04.041Z"); // Pass your date.
Date timestamp = Date.from(instant);
Document query = Filters.eq("timestamp", timestamp);

Perfect solution if you are using Mongo Cursor from JAVA programming.

Nolequen
  • 3,032
  • 6
  • 36
  • 55
nagu n
  • 1