Java/MongoDB query by date

Question

I stored a value as a java.util.Date() in my collection, but when I query to get values between two specific dates, I end up getting values outside of the range. Here's my code:

to insert

BasicDBObject object = new BasicDBObject();
...
object.put("dateAdded", new java.util.Date());
collection.insert(object);

to query

BasicDBObject query = new BasicDBObject();
query.put("dateAdded", new BasicDBObject("$gte", fromDate));
query.put("dateAdded", new BasicDBObject("$lte", toDate));
collection.find(query).sort(new BasicDBObject("dateAdded", -1));

when I query between Wed Jul 27 16:54:49 EST 2011 and Wed Jul 27 16:54:49 EST 2011 (basically fromDate = toDate), I get objects with dates like Tue Jul 26 09:43:37 EST 2011 which should definitely not be possible. What am I missing here?

score 55 · Accepted Answer · answered Jul 27 '11 at 10:52

55

What you're doing is querying only with {$lte: toDate} losing $gte operator in the key overwrite. What you want is:

query.put("dateAdded", BasicDBObjectBuilder.start("$gte", fromDate).add("$lte", toDate).get());

answered Jul 27 '11 at 10:52

pingw33n

12,292
2
37
38

Excellent tip! So basically every time I want to query on the same field with different criteria I have to use the builder right? good to know! Where did you find this? is there any documentation I'm not aware of? (since the mongodb official java tutorial is quite poor!) – Guillaume Jul 27 '11 at 22:27
1

@Guillaume Not exactly. You can also create and populate `BasicDBObject`, the builder is just helper that allows to do it inside a single satement. The problem is that `DBObject` (like all `Map`s in Java) can contain only unique keys. And you lose the previous value when putting new one with the same key. Also official tutorial has example on how to use multiple operators on the same field: http://www.mongodb.org/display/DOCS/Java+Tutorial#JavaTutorial-GettingASetofDocumentsWithaQuery – pingw33n Jul 28 '11 at 08:11
3

Surprisingly hard finding the answer to that question. – Fredrik Wallenius Sep 26 '12 at 19:29

score 6 · Answer 2 · answered Apr 28 '15 at 07:09

If you are using MongoTemplate of Spring-Data Mongodb, you can do the same in following way:-

public List<Link> getLinksBetweenDate(Date startDate, Date endDate) {
            Query query = new Query().addCriteria(Criteria.where("updatedOn").gt(startDate).lte(endDate));
            return mongoTemplate.find(query, Link.class);
        }

score 5 · Answer 3 · edited May 04 '15 at 23:17

5

Using mongo client 3.0

DateFormat format = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'", Locale.ENGLISH);
Bson filter = new Document("$gte", format.parse("2015-05-01T00:00:00Z")).append("$lt", format.parse("2015-05-02T00:00:00Z"));
long count = db.getCollection("colection").count(new Document("field",filter) );

edited May 04 '15 at 23:17

Aify

3,543
3
24
43

answered May 04 '15 at 22:39

Miguel Angel

419
3
4

What does long count return? What is contained within this variable? How do you extract database entries from this variable? – user3388925 Nov 06 '16 at 07:45
You should never enclose `Z` within single quotes. Check the correct way at https://stackoverflow.com/a/65710515/10819573 – Arvind Kumar Avinash Jan 14 '21 at 11:47

score 4 · Answer 4 · edited Jun 03 '15 at 19:57

Search Date with specific format following steps are that this also work in mongo 3.0

SimpleDateFormat simpleDateFormat = new SimpleDateFormat ("yyyy.MM.dd");

First convert your date in your specific format, you can use any format and parse your date with above format and then pass in query.

String date ="2014.01.02"; 
String data1 ="2014.01.10";
Date startDate = simpleDateFormat.parse(date);  
Date endDate = simpleDateFormat.parse(date1);

BasicDBObject query = new BasicDBObject("fieldName",
   new BasicDBObject("$gte",startDate).append("$lt",endDate ));

So base on your requirement you can search date by giving argument in BasicDBobject.

score 2 · Answer 5 · answered Jan 29 '20 at 22:39

You can do

    import org.bson.conversions.Bson;
    import static com.mongodb.client.model.Filters.*;
    import java.text.SimpleDateFormat;

    DateFormat format = new SimpleDateFormat("yyyy-MM-dd", Locale.ENGLISH);
    Bson filter = Filters.and(gte("date_field", format.parse("2018-10-01")),
                            lt("date_field", format.parse("2019-10-01")))

score 1 · Answer 6 · answered Feb 15 '20 at 17:19

1

I approach the query like this

new Document("datetime",Document.parse(
    "{$gte: ISODate('" + new SimpleDateFormat("yyyy-MM-dd").format(fromDate) + "'), 
     $lte: ISODate('" + new SimpleDateFormat("yyyy-MM-dd").format(endDate) + "')}"
))

answered Feb 15 '20 at 17:19

Abhilash

457
5
9

score 0 · Answer 7 · edited Sep 24 '21 at 08:21

0

If you want the exact answers to do one more thing when you applying the date range use the UTC time for the same.

On your local machine, you can use the below code to find the UTC time from your machine or server:

java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("UTC"));

edited Sep 24 '21 at 08:21

JW Geertsma

857
3
13
19

answered Sep 24 '21 at 06:13

Sulabh Jain

1
3

Java/MongoDB query by date

7 Answers7

Linked

Related