Going bonkers with date queries...

[Alex Paransky]

So, 

This works in the shell and returns results:

db.events.find(
    {
        d : {
            $gte : ISODate('2015-03-19 16:00:12.000Z'),
            $lt : ISODate('2015-03-19 16:00:30.000Z')
        }
    }
)

But this DOES NOT work in the shell and no results are returned:

db.events.find(
    {
        d : {
            $gte : {$date : '2015-03-19 16:00:12.000Z'},
            $lt : {$date : '2015-03-19 16:00:30.000Z'}
        }
    }
)

However, when I try to execute the first command from Java:

            JSON.parse(
                    "    {\n" +
                    "        d : {\n" +
                    "            $gte : ISODate('2015-03-19 16:00:12.000Z'), \n" +
                    "            $lt : ISODate('2015-03-19 16:00:30.000Z')\n" +
                    "        }\n" +
                    "    }\n" +
                    ")"
            );

 JSON.parse cannot seem to parse ISODate and throws the following exception (in debugger I see that it's stuck on 'I' in ISODate):

Caused by: com.mongodb.util.JSONParseException:
{
        d : {
            $gte : ISODate('2015-03-19 16:00:12.000Z'),
            $lt : ISODate('2015-03-19 16:00:30.000Z')
        }
    }
)
                                   ^
 at com.mongodb.util.JSONParser.parse(JSON.java:208)
 at com.mongodb.util.JSONParser.parseObject(JSON.java:241)
 at com.mongodb.util.JSONParser.parse(JSON.java:205)
 at com.mongodb.util.JSONParser.parseObject(JSON.java:241)
 at com.mongodb.util.JSONParser.parse(JSON.java:205)
 at com.mongodb.util.JSONParser.parse(JSON.java:155)
 at com.mongodb.util.JSON.parse(JSON.java:93)
 at com.mongodb.util.JSON.parse(JSON.java:74)

Can anyone help me figure out how to convert ISODate into something that JSON.parse?

Thanks.

-AP_

[Alex Paransky]

Just tried this as described on this page:  

db.events.find(
    {
        d : {
            $gte : { "$date" : { "$numberLong" : "1426780800000" } },
             $lt : { "$date" : { "$numberLong" : "1426780801000" } }
        }
    }
)

The shell still produces no results.

I am sure I am missing something.  

-AP_

[Alex Paransky]

Even this does not work, no results are produced:

db.events.find(
    {
        d : {
            $gte : { $date : { $numberLong : 0 } }
        }
    }
)

I really DO have objects that should match, also when I use ISODate in the shell it works:

{
    "_id" : ObjectId("5510418faa242356629cd69e"),
    "uid" : "97d50fdc-8b81-41eb-82da-9d86984dbedc",
    "v" : 75,
    "T" : "qos",
    "d" : ISODate("2015-03-19T16:00:12.000Z"),
    "da" : "This is some constant data because we don't really know what is going to be in this field going forward",
    "TT" : "mediasizechange"

}

-AP_

[Jeff Yemin]

Hi Alex,

I agree this is confusing.  The problem you're encountering is that the Java driver supports only MongoDB Extended JSON Strict Mode while the MongoDB shell only support Shell Mode.  There is currently no solution to this except to convert manually from one to the other.

In practice, this isn't usually an issue, because it's not often that a real application will want to query based on hard-coded dates like that.  But for quick testing of queries, it would be nice if this worked as you wished.

The soon-to-be-released 3.0.0 Java driver, however, will support Shell Mode, so pretty soon we'll have a solution for you.

Regards,

Jeff

--
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/ccb13733-71e4-49a7-97be-05b8cc7bd3eb%40googlegroups.com.

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

[Alex Paransky]

Jeff,

Thanks for your reply.  I got 3.0.0-rc1 all setup, however, when I execute this:

        db  
                        .command(

                            "{" +

                            "    aggregate: 'events'," +

                            "    pipeline: [" +

                            "        {" +

                            "            $match : {" +

                            "                T : 'qos'," +

                            "                TT : 'buffering'" +

                            "            }" +

                            "        }" +

                            "    ]" +

                            "}"

                        );

I get this error:

"errmsg" -> "no such command: {

    aggregate: 'events',

    pipeline: [

        {

            $match : {

                T : 'qos',

                TT : 'buffering'

            }

        }

    ]

}

"

I am most likely not doing something correctly.  Can you point me to the docs or a code (unit test) that may help me figure out how to make this invocation work?

Thanks.

-AP_

[Jeff Yemin]

Hi Alex,

I see. 

Does the final version does this text file need to be consumed by both a Java program and the shell?  If just a Java program, JSON.parse will handle strict mode JSON as defined in http://docs.mongodb.org/manual/reference/mongodb-extended-json/, e.g.

    { "d" : { "$gt" : { "$date" : "2015-03-26T13:40:40.335Z" }, "$lt" : { "$date" : "2015-03-26T14:40:40.335Z" } } }

The shell, however, will not parse this query, as you have already discovered.

The 3.0.0-rc1 driver can be downloaded from the Maven Central repository:

<dependency> <groupId>org.mongodb</groupId> <artifactId>mongo-java-driver</artifactId> <version>3.0.0-rc1</version> 

</dependency> 

3.0.0 will be release in the coming weeks.

Regards,

Jeff

On Thu, Mar 26, 2015 at 12:03 AM, Alex Paransky <ap...@standardset.com> wrote:

Jeff,

So, I am not really clear what you mean by "manually" convert.  

Our goal, is to have a "report definition" in a text file.  Part of that definition is a query template.  In the final system, the date "values" will be replaced with user entered text, so the text file will not have literal dates, but rather replacement tokens such as ... ISODate($dateFrom) (or something to this effect).  So, we would read the template, replace placeholders with user entered values, and hopefully submit to Mongo from Spring's RestTemplate.  This is what we are really trying to accomplish.

What are the alternatives?  Is the 3.0.0 mongo driver available on GitHub so we can start playing with it?  Can you elaborate on what you mean by "manually" convert?

Thanks.

-AP_

On Wednesday, March 25, 2015 at 8:08:58 PM UTC-7, Jeff Yemin wrote:

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/5fa92fc2-876c-46a7-ac21-aabf0fb95f1f%40googlegroups.com.

[Alex Paransky]

Jeff,

Executing only from Java would be fine for now, however, it would be nice in the future to "prototype" the query in a console and then paste it into a Java app with minimal changes.

I added this dependency:

<dependency>     <groupId>org.mongodb</groupId>     <artifactId>mongo-java-driver</artifactId>     <version>3.0.0-rc1</version> </dependency>

This code WORKS (countResult2 = 48).  It's not really what I want to do, but it serves to show a point that something is still not working with the other option.

final long
    countResult2 =
        mongoTemplate
            .getDb()
            .getCollection("events")
            .count(
                BasicDBObjectBuilder
                    .start(
                        "d",
                        BasicDBObjectBuilder
                            .start()
                            .add("$gte", ISODateTimeFormat.dateTime().parseDateTime("2015-03-19T16:00:00.000Z").toDate())
                            .add("$lt",  ISODateTimeFormat.dateTime().parseDateTime("2015-03-19T16:00:01.000Z").toDate())
                            .get()
                    )
                    .get()
            );

But this still does not work and returns ZERO count (countResult1 = 0):

final long
    countResult1 =
        mongoTemplate
            .getDb()
            .getCollection("events")
            .count(
                (DBObject)
                JSON.parse(
       "{ \"d\" : { \"$gt\" : { \"$date\" : \"2015-03-19T16:00:00.000Z\" }, \"$lt\" : { \"$date\" : \"2015-03-19T16:00:01.000Z\" } } }"
                )
            );

Again, this is running with 3.0.0-rc1 driver...

Thanks.

-AP_

[Jeff Yemin]

Hi Alex,

Sorry, I should have been more clear about this: for a variety of reasons, we were not able to add support for Shell Mode to the existing JSON class. Instead, there is a new class, JsonReader, that handles it.  

However, in trying to get your example to work I found a bug in the JsonReader ISODate parsing code, which I reported as JAVA-1723.  There is also a usability issue which I reported as JAVA-1721.   

We'll try to get at least  JAVA-1723 fixed in the first point release after 3.0.0 ships. JAVA-1721 will have to wait until 3.1, but it's not a blocker, it just means you'll have to write a bit more code instead of relying on a helper method that we'll be providing.

Hang tight, and please put a watch on these issues so you can track when they've been resolved.

Regards,

Jeff

To view this discussion on the web visit https://groups.google.com/d/msgid/mongodb-user/84218c4d-20f3-4963-a0cd-e8bd06d0be46%40googlegroups.com.

[Alex Paransky]

Jeff, 

Thank you so much for your help and support.  I guess when there are a few issues at play things get a bit difficult.  

So, with exception of usability issues (console vs driver), I believe the other stuff had to do with my code.

The following WORKS!

                            "{" +
                            "    aggregate: 'events'," +
                            "    pipeline: [" +
                            "        {" +
                            "            $match : {" +

                            "               d : { " +
                            "                   $gte : { $date : '2015-03-19T16:00:00.000Z' }, " +
                            "                   $lt : { $date : '2015-03-19T16:00:01.000Z' } " +
                            "               }" +
                            "            }" +
                            "        }" +
                            "    ]" +
                            "}"

A few observations:

1) The format that is recognized has a T in it: 2015-03-19T16:00:00.000Z, without the T things don't quite work.  This is probably related to https://jira.mongodb.org/browse/JAVA-1723 although all the examples on http://en.wikipedia.org/wiki/ISO_8601 do show the T between the date and time portions so perhaps things are working as designed?  Perhaps the Shell is too lenient is recognizing the ISO8601 date format without the T?  This was also the confusion when pasting and trying to convert from ISODate to $date usage for Java.

2)   In later messages in this thread, somehow $gte was replaced with $gt which stopped working as the difference between two timestamps was 1 second and naturally nothing matched being $gt in than narrow range.

Anyhow, all is well.  

Thanks for your help.

-AP_

...