I can't reproduce this issue.  How was the document inserted?

Jenny, the docs were updated using following type of query:

update zips set subs = { "ldate" : new Date('Dec 17, 1941')}

I am attaching the data set. zips_mod.json.zip

I suspect mongodb perhaps prepends '$date' key to comply with json specs (not sure though).

I am able to reproduce it now.  This happens with a date sub-field.  Refer to the attached "Issue8855" screenshot.  "date1" is a top-level field and "date2" is a sub-field.  "date2" is returned in the { "$date" : ... } format with a SELECT JSON query.

Emil, would it be possible for the JDBC driver to return "date2" as { "date2" : ISODate(...) } just like how it is in the mongo shell?

This display format ((with $key entities) occurs not only for Date values, but also for Pattern, ObjectId, UUID, BSONTimestamp, MinKey, MaxKey, DBRef values stored as sub-fields.

When using  SELECT JSON statements, MongoResultSet.getObject() returns BasicDBObject instances for objects containing sub-fields. Then ADS calls .toString() on this object. The BasicDBObject.toString() method serializes this object to the MongoDB extended JSON format.

The serialization is done by the serializers defined inside the com.mongodb.util.JSONSerializers.getLegacy() method, but unfortunately I cannot replace these serializers with custom ones which would have helped to alter the result of BasicDBObject.toString() .

We should establish what kind of container should MongoResultSet.getObject() return for this scenario (a BasicDBObject instance or something else). Inside the .getObject() method, I could iterate recursively over this object, build the desired string and return it instead of the BasicDBObject instance, but I think this is not desirable and might generate issues on other ADS modules (Table Data Editor etc).

Jenny, do you have any proposals on how to solve this issue (from API calls perspective between the JDBC driver and how ADS renders it)?

I could implement custom JSON serializers and provide an API method inside the JDBC driver, similar to the com.mongodb.util.JSON.serialize() one. Then from ADS (after MongoResultSet.getObject() is called) you can call this method and obtain the expected display string, while still getting the BasicDBObject instance that might be used in other modules.

Let's have custom JSON serializers and an API in the JDBC driver as you proposed.  This is probably the best approach so that we can tailor the output to what we need.

While trying to verify this issue on ADS 14-beta-55, I noted following behavior:

1) When I update date as db.zips.update({}, {$set :{subs : {ldate : new ISODate()}}}, {multi : update})  and execute the following query

db.zips.find().pretty()

the mongo shell returns field 'subs' as

"subs" : { "ldate" : ISODate("2013-07-07T08:14:23.830Z")

which is the UTC datetime value of current date.

2) When we run the query select json * from zips limit 10, the field subs is returned as

{ "ldate" : ISODate("2013-07-07 13:59:23.830") } 

which is the ISODate object of current local datatime value.

3) When we run the query select flatten * from zips limit 10, the field subs is returned as

2013-07-07 13:59:23.83

Could you please confirm if this is desired behavior? I expected the ISODate object representation to be same in (2) and (1).

The behaviour from point 2) occurs because the original MongoShell CLI uses the GMT format, while we use the default locale in our date serializer (i.e. what's displayed for the select json * from zips limit 10 query).

Jenny, should we use the GMT format to display the ISODate() value? In this case the string argument of the ISODate() function that appears in INSERT statements etc should also be parsed in GMT format (currently is parsed in the locale timezone format).

Then what about Date() constructor? Should we keep parsing the string argument using local timezone format? The MongoShell CLI ignores the string argument for this function (expected behaviour, see this reported issue), therefore timezone compatibility between MongoSQL and MongoShell is not required for this function.

The behaviour from point 3) is the expected one because the values from the subs.ldate column are java.util.Date objects and are rendered according to the format chosen by the user under ADS Options -> Results Format -> MongoDB -> DataType == DATE -> Format .

2) Emil, let's display the value in GMT format (same as MongoShell CLI) when returning the JSON data.

     { "ldate" : ISODate('2013-07-07T08:14:23.830Z') }

3) It is correct that this can be controlled by the Results Format option.  For example, if you choose the format to be EEE, d MMM yyyy HH:mm:ss Z, you'll see the time zone information (Z) and it is in your local time zone.

   We'll update the behavior of the [None] format option to display the date values in GMT format (issue #9825).

The GMT/UTC timezone is now used both to parse the string argument of the ISODate() function as well as when displaying such values in JSON format (see issue's description).

Hi Emil,

Checking on ADS 14-rc-3, observed that JSON format still returns datetime in local timezone rather than GMT/UTC. (Pls refer to attached mongo_CLI.png and json_dates.png screenshots).

The fix must be in later release right?

The fix must be in later release right?

Yes, as long as the "Fixed build" tag doesn't contain the "ADS x.x.x" prefix, it means that the mongo-jdbc build was not yet integrated into ADS.

Verified on ADS 14-rc-8 that

1) datetime values are returned in GMT/UTC when returning JSON data.

2) When 'NONE' results format option is specified, the dates are returned in UTC/GMT else it's returned as local datetime.