Date Queries are not producing results ( difference between $date and ISODate()?

[Wilton]

Hi All,

I'm trying to understand why two queries that *should* produce the
same results are not. It appears there is a difference in the way
Mongo treats dates.

Here are my two examples:


robotXXX:PRIMARY> db.market.find( { "tic" : "AAU" , "market" :
"AMEX" } );
{ "_id" : ObjectId("4da8eb551eeaf58bb2e0051b"), "adj_closing" : 0.28,
"adjustment" : 1, "close_date" : ISODate("2011-04-15T00:00:00Z"),
"closing" : 0.28, "high" : 0.28, "isin" : "CA03349A2002", "low" :
0.26, "market" : "AMEX", "opening" : 0.27, "ric" : "AAU.A", "sma200" :
2.6956999999999995, "tic" : "AAU", "updated_at" :
ISODate("2011-04-19T00:18:56.658Z"), "volume" : 76100 }

Note this query returns a record with the close_date = 2011-04-15.

However, if I run a query that specifies a date range, it fails:


robotXXX:PRIMARY> db.market.find( { "tic" : "AAU" , "market" :
"AMEX" , "close_date" : "2011-04-15T00:00:00Z"} );
robotXXX:PRIMARY>


This same query, with ISODate, returns a result:

robotXXX:PRIMARY> db.market.find( { "tic" : "AAU" , "market" :
"AMEX" , "close_date" : ISODate("2011-04-15T00:00:00Z")} );
{ "_id" : ObjectId("4da8eb551eeaf58bb2e0051b"), "adj_closing" : 0.28,
"adjustment" : 1, "close_date" : ISODate("2011-04-15T00:00:00Z"),
"closing" : 0.28, "high" : 0.28, "isin" : "CA03349A2002", "low" :
0.26, "market" : "AMEX", "opening" : 0.27, "ric" : "AAU.A", "sma200" :
2.6956999999999995, "tic" : "AAU", "updated_at" :
ISODate("2011-04-19T00:18:56.658Z"), "volume" : 76100 }

Can somebody please explain this difference? The problem is I'm using
the Java Mongo driver, which is *not* using the ISODate() format for
the queries, so I'm getting incorrect results in a lot of cases.

[Bernie Hackett]

> robotXXX:PRIMARY> db.market.find( { "tic" : "AAU" , "market" :
> "AMEX" , "close_date" : "2011-04-15T00:00:00Z"} );
> robotXXX:PRIMARY>

MongoDB stored the date as a datetime (http://bsonspec.org/#/
specification). In this query you are trying to query on a string.
Since you don't have any documents where 'close_date' is just a string
you don't get any results.

> The problem is I'm using
> the Java Mongo driver, which is *not* using the ISODate() format for
> the queries, so I'm getting incorrect results in a lot of cases.

You need to use the Java Date class. Also see http://cookbook.mongodb.org/patterns/date_range/
for some examples in other languages.

[Wilton]

I *am* using the Java date class. Here is a code snippet and the
query that it produces:

java.util.Date marketDate = xxx
BasicDBObject query = new BasicDBObject();
query.put("tic", ticker.getTicker());
query.append("market", ticker.getExchange());
query.append("close_date", new BasicDBObject("$lte", marketDate));

produces:

{ "tic" : "AAU" , "market" : "AMEX" , "close_date" : { "$lte" :
{ "$date" : "2010-05-06T07:00:00Z"}}}

On May 2, 4:17 pm, Bernie Hackett <ber...@10gen.com> wrote:
> > robotXXX:PRIMARY> db.market.find( { "tic" : "AAU" , "market" :
> > "AMEX" , "close_date" : "2011-04-15T00:00:00Z"} );
> > robotXXX:PRIMARY>
>
> MongoDB stored the date as a datetime (http://bsonspec.org/#/
> specification). In this query you are trying to query on a string.
> Since you don't have any documents where 'close_date' is just a string
> you don't get any results.
>
> > The problem is I'm using
> > the Java Mongo driver, which is *not* using the ISODate() format for
> > the queries, so I'm getting incorrect results in a lot of cases.
>

> You need to use the Java Date class. Also seehttp://cookbook.mongodb.org/patterns/date_range/

[Bernie Hackett]

Are you saying that the query produced is incorrect? You didn't
provide the code that created the date object or the result returned
by the query so I'm not sure what's wrong.

[Nat]

Can you show how you get the incorrect result from java driver?

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[Wilton]

In that previous message, I included the text representation of the
query as displayed in my eclipse debugger. The query that is being
generated appears to be looking for text representations of the date
as you described it, rather than wrapping the dates with the
"ISODate()" function that you said must be included.

So what I'm suggesting is that maybe there's a bug in the Java driver.

[Wilton]

The issue appears to be twofold:

In the BasicDBObject class, the code that generates the string output
simply calls JSON.serialize as such:

public String toString(){
return JSON.serialize( this );
}

If we look at JSON.serialize(), we find the following:

if (o instanceof Date) {
Date d = (Date) o;
SimpleDateFormat format =
new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
format.setCalendar(new GregorianCalendar(new
SimpleTimeZone(0, "GMT")));
serialize(new BasicDBObject("$date", format.format(d)), buf);
return;
}

We can see here that it is NOT wrapping the dates with the "ISODate()"
function, but simply formatting it via UTF. If what you said is
correct, then this is wrong as it is causing the date queries to be
treated as Strings, rather than actual dates.

[Nat]

JSON.serialize() is only used to perform JSON representation of BSON
document. It's not being used by Java driver in sending the date over
in BSON format. I think it has something to do with the timezone
(2010-05-06T07:00:00Z vs 2010-05-06T07:00:00Z). You might want to
create Date from GMT Calendar.

[Antoine Girbal]

Nat's comment is correct, the JSON serializer/deserializer is only
used on the client side to generate a JSON representation.
This is used to output as string or to do import/export.
The query is internally represented in BSON format and uses a Date
type.

If it doesnt work for you, try just doing a find() with no query and
then get the Date object of the 1st doc.
Display the date and see if you get what you expect, the timezone
could be different.
Try to use GMT everywhere (client and server) if you plan on doing
date queries.

[Wilton]

Thanks for the info, guys.

[Craig Lawrence]

Hi Wilton.

Did you ever manage to resolve this issue?

I have exactly the same "problem".

new BasicDBObject("eventTime",  BasicDBObjectBuilder.start("$gte", startDate).add("$lt", endDate).get());

results in:

{ "eventTime" : { "$gte" : { "$date" : "2013-03-11T00:00:00.000Z"} , "$lt" : { "$date" : "2013-03-12T00:00:00.000Z"}}}


...and this gives me now results when search on this query of course gives me 0 results.

Thanks in advanced.

[Antoine Girbal]

Craig,

the query looks correct.

Do you mean that you get no results from running the query in Java?

Can you give sample documents from querying in the shell? Do you have a shell query that matches properly?

[Craig Lawrence]

Hi there,

The Java API gives me no results.

However this query when executed via the mongo shell:

db.logEntry.find({ "eventTime" : { "$gte" : ISODate("2013-01-01T00:00:00.000Z") , "$lt" : ISODate("2013-01-02T00:00:00.000Z")}})

Returns results as expected:

{ "_id" : ObjectId("52509b38975afebfc12ab3e1"), "action" : "testAction", "deleted" : false, "entityId" : "11111", "entityType" : "testType", "eventTime" : ISODate("2013-01-01T00:00:00Z"), "saveTime" : ISODate("2013-10-05T23:05:28.604Z")}

I've masked and removed some fields for obvious reasons but the structure is the same.

Thanks

[Asya Kamsky]

Can you show your Java code beyond creating a query object, including the find and the code where you fetch the result set and how you determine that no documents are returned...

Asya

--

You received this message because you are subscribed to the Google Groups "mongodb-user"
group.
 

For other MongoDB technical support options, see: http://www.mongodb.org/about/support/.
---

You received this message because you are subscribed to the Google Groups "mongodb-user" group.

To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user...@googlegroups.com.

To post to this group, send email to mongod...@googlegroups.com.

Visit this group at http://groups.google.com/group/mongodb-user.

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/7a37b301-6499-41c6-af9f-b1040d182942%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.