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?

pingw33n · Accepted Answer · 2011-07-27 10:52:52Z

56

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, 2011 at 10:52

pingw33n

12.6k2 gold badges39 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Guillaume Over a year ago

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!)

pingw33n Over a year ago

@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 Maps 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: mongodb.org/display/DOCS/…

Fredrik Wallenius Over a year ago

Surprisingly hard finding the answer to that question.

Vaibhav · Accepted Answer · 2015-04-28 07:09:47Z

6

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);
        }

answered Apr 28, 2015 at 7:09

Vaibhav

2,1634 gold badges25 silver badges26 bronze badges

Comments

Aify · Accepted Answer · 2015-05-04 23:17:37Z

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 4, 2015 at 23:17

Aify

3,5573 gold badges26 silver badges43 bronze badges

answered May 4, 2015 at 22:39

Miguel Angel

4493 silver badges6 bronze badges

2 Comments

user3388925 Over a year ago

What does long count return? What is contained within this variable? How do you extract database entries from this variable?

Arvind Kumar Avinash Over a year ago

You should never enclose Z within single quotes. Check the correct way at stackoverflow.com/a/65710515/10819573

Andie2302 · Accepted Answer · 2015-06-03 19:57:28Z

4

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.

edited Jun 3, 2015 at 19:57

Andie2302

4,9254 gold badges26 silver badges46 bronze badges

answered May 12, 2015 at 12:42

Hemang Rami

813 bronze badges

Comments

Anu · Accepted Answer · 2020-01-29 22:39:28Z

3

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")))

answered Jan 29, 2020 at 22:39

Anu

3,4305 gold badges32 silver badges52 bronze badges

Comments

Abhilash · Accepted Answer · 2020-02-15 17:19:15Z

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, 2020 at 17:19

Abhilash

5775 silver badges10 bronze badges

Comments

JW Geertsma · Accepted Answer · 2021-09-24 08:21:45Z

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, 2021 at 8:21

JW Geertsma

8753 gold badges18 silver badges22 bronze badges

answered Sep 24, 2021 at 6:13

Sulabh Jain

13 bronze badges

Collectives™ on Stack Overflow

Java/MongoDB query by date

7 Answers 7

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related