Date getting compared as string in MongoDB

Question

I am trying to compare dates in the below collection

$db.notifications.find() { "_id" : ObjectId("51a4467be4b0142fc8b80eda"), "time" : "Tue May 28 11:24:03 IST 2013", "KEY" : "VALUE1" } { "_id" : ObjectId("51a4467be4b0142fc8b80edb"), "time" : "Tue May 28 11:24:03 IST 2013", "KEY" : "VALUE2" }

using

$db.notifications.find({ "time" : { "$gt" : "Fri May 31 00:00:00 IST 2013"}})

but what I am observing is that it is comparing treating "time" as String an not as Date and thus the second command is returning me both the entries.

$ db.notifications.find({ "time" : { "$gt" : "Fri May 31 00:00:00 IST 2013"}}) { "_id" : ObjectId("51a4467be4b0142fc8b80eda"), "time" : "Tue May 28 11:24:03 IST 2013", "KEY" : "VALUE1" } { "_id" : ObjectId("51a4467be4b0142fc8b80edb"), "time" : "Tue May 28 11:24:03 IST 2013", "KEY" : "VALUE2" }

Can you please help me with the correct query and also let me know how that correct version can be written in JAVA.

Thanks much !!

EDIT1 :

I am saving date using the below code

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS");
dateFormat.setLenient(false);
dateFormat.setTimeZone(TimeZone.getTimeZone("UTC")); 
String nowString = dateFormat.format(new Date()); 
Date nowDate = dateFormat.parse(nowString); 
input.put("formattedDate", nowDate);
DBCollection collection = db.getCollection(<collection name>);
DBObject dbObject = (DBObject) JSON.parse(input.toString());
collection.insert(dbObject);"

and querying using

DBObject query = QueryBuilder.start().put("formattedDate")
                                     .greaterThan(prevDate).get();
DBCursor cursorDocJSON = collection.find(query);

I see that I am saving "time" as Date only. Why is still there an issue?

it's doing that because you are storing it as a string!

Asya Kamsky
– Asya Kamsky

2013-05-28 18:31:29 +00:00
Commented May 28, 2013 at 18:31 — Asya Kamsky
– Asya Kamsky, Commented May 28, 2013 at 18:31

Trisha · Accepted Answer · 2013-05-28 17:13:17Z

1

It looks like your date was stored as a String and not as an ISODate. If it were stored as a real date and not a string, you'd see something like:

> db.MyCollection.find()
{ "_id" : ObjectId("51a4e4aa3004bc68e4f499e5"), "date" : ISODate("2000-09-29T23:00:00Z") }

Make sure when you're saving the "time" field that you're inserting it as a Date. If you're using the Java driver to insert the values, you need to make sure that time field is a java.util.Date when you add it to DBObject (assuming you're using the Java driver directly and not some third party library).

When you've inserted it as a date then you should be able to perform the types of queries you want, using ISODate values from the shell or Date via the Java driver.

answered May 28, 2013 at 17:13

Trisha

3,9511 gold badge28 silver badges40 bronze badges

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

2 Comments

user2429238 Over a year ago

I am saving date using the below code SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS").; dateFormat.setLenient(false); dateFormat.setTimeZone(TimeZone.getTimeZone("UTC")); String nowString = dateFormat.format(new Date()); Date nowDate = dateFormat.parse(nowString); input.put("time", nowDate); DBCollection collection = db.getCollection(<collection name>); DBObject dbObject = (DBObject) JSON.parse(input.toString()); collection.insert(dbObject); Can you please help me understand my mistake here.

Trisha Over a year ago

The code in your comment is creating a new Date object (new Date()), using SimpleDateFormatter to turn it into a String (dateFormat.format(new Date()), turning it back into a Date object (dateFormat.parse(nowString)). You'll get the same result in a lot less lines of code by doing input.put("time", new Date()); But that will still be wrong. What type is input and what else is in it?

PermGenError · Accepted Answer · 2013-05-28 15:49:31Z

0

I think your date should be an ISODate object. currently its a string in your query. try something like below.

db.coll.find({"time" : { $gte : new ISODate("2013-05-28T20:18:00Z") }});

Also, related link

answered May 28, 2013 at 15:49

PermGenError

46.5k8 gold badges90 silver badges107 bronze badges

1 Comment

user2429238 Over a year ago

this is the query that is getting executed. >>> db.notifications.find({ "time" : { "$gt" : { "$date" : "2013-05-24T18:30:00.000Z"}}})

Collectives™ on Stack Overflow

Date getting compared as string in MongoDB

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related