Same error with ISODate

insert into bug values(

{

"meta" : {

"dateTime" : ISODate("2011-08-23T00:00:00Z")

}

})

go

I think the Date() function used under MongoShell is locale-dependant. On my machine your scenario fails under MongoShell (document is inserted, but the date is wrong, see below):

> db.jira_2.insert(  { "version" : "1.6.3",  "uptime" : 4712,  "uptimeEstimate" : 3844,  "localTime" : Date( "Wed Oct 13 20:52:15 2010" )  }  )
> db.jira_2.find()
{ "_id" : ObjectId("5167405f99ab62599120aad8"), "version" : "1.6.3", "uptime" : 4712, "uptimeEstimate" : 3844, "localTime" : "Fri Apr 12 2013 01:59:43 GMT+0300 (EEST)" }

The patterns recognized by the Mongo JDBC Driver for the Date() function are:

MMM dd, yyyy or yyyy-MM-dd

and for DateTime() / TimeStamp() :

yyyy-MM-dd HH:mm:ss or yyyy-MM-dd HH:mm:ss.SSS

Maybe we should use for each date / time function (inside the JDBC driver) the patterns defined in Options -> General -> Appearance -> Date Format etc.

However, in this case you should provide some API in aqua-external so that I can retrieve these patterns and use them inside ParserUtils.getValue() method, when parsing the corresponding date / time function.

Same error with ISODate

For this case there is a pattern missing, I'll fix it.

Added missing pattern for ISODate().

The patterns specified in Options -> General -> Appearance -> Date Format are for display only.  When submitting a SQL statement, date/timestamp values must be in JDBC format.

Emil, when a JSON document is specified in the SQL INSERT statement, does the JDBC driver process the JSON document at all?

Yes, the JSON documents are processed because the MongoDB Java Library works with BasicDBObject instances, which are actually java hashmaps. For example:

{ version: "1.6.3", timestamp: ISODate("2011-08-23T00:00:00Z") }

is converted to a hashmap

SimpleDateFormat parser = new SimpleDateFormat(pattern);

java.util.Date date = parser.parse("2011-08-23T00:00:00Z");

new BasicDBObject("version", "1.6.3").put("timestamp", date);

As you can see, any JSON value is converted to the corresponding java object. This object is then processed by the encoder and sent in binary format as BSON entity to MongoDB Server.

What should we do with this issue? ISODate() missing pattern has been added in the 1.1.1 drop. Should I also add the corresponding parsing pattern for Date("Wed Oct 13 20:52:15 2010") ?

Let's document the formats that are supported by all the timestamp functions in MongoSQL.  When using the SQL INSERT/UPDATE commands, the timestamp values need to be in a format that MongoSQL supports.

Here are the patterns currently supported by the Mongo JDBC driver:

refresh 

http://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/MongoDB/page/Query-Reference/Query-Reference#date_and_timestamp_support

to see how I incorporated the additional time formats.

Verified in 14.0-dev-48