CODEWITHSUNDEEP
mongodb
R2D2
R2D2

Reputation: 10737

Inconsistent Date field in mongodb , why happen , how to identify and fix

I have identified recently date fields in my mongod 4.0 with such a content:

 "last_update" : ISODate("-229-05-06T07:23:23Z")
 "last_update" : ISODate("50170-12-13T06:03:34Z")
 "last_update" : ISODate("0000-07-23T05:19:55Z")

So my question:

  1. Is mongodb allowing such freedom for date fields ( signed 64bit integer of type date ) ?

According to this :

https://github.com/mongodb/specifications/blob/master/source/extended-json.rst#conversion-table

It seems like the format is OK?

Datetime [year before 1970 or after 9999] {"$date": {"$numberLong": <64-bit signed integer giving millisecs relative to the epoch, as a string>}}

Attempts to insert it from mongo shell ISO date helper as expected did not allowed me:

 MongoDB Enterprise mongos> db.test.insert({  "created" : ISODate("-229-05-06T07:23:23Z")  })
 2021-01-29T11:47:53.484+0100 E QUERY    [js] Error: invalid ISO date: -229-05-06T07:23:23Z :
 ISODate@src/mongo/shell/types.js:65:1
 @(shell):1:31
 MongoDB Enterprise mongos>

But at the same time insert affected fields from the original document in another collection was not having issues:

 MongoDB Enterprise mongos> var x = db.theAffectedCollection.findOne({_id:ObjectId("5c6e8c6ce0ebbb309ce0dc06")}, 
{created:1,last_update:1})
 MongoDB Enterprise mongos> use test
 MongoDB Enterprise mongos> db.test.insert(x)
 WriteResult({ "nInserted" : 1 })
 MongoDB Enterprise mongos> db.test.find()
{ "_id" : ObjectId("5c6e8c6ce0ebbb309ce0dc06"), "created" : ISODate("-229-05-06T07:23:23Z"), "last_update" : ISODate("-229-05-06T07:23:23Z") }

mongoexport result:

 "created":{"$date":"-0229-05-06T07:23:23.000Z"}

Luckily the document _id timestamp show the exact creation date:

 ObjectId("5c6e8c6ce0ebbb309ce0dc06").getTimestamp()
 ISODate("2019-02-21T11:33:00Z")

So I could easily fix it ...

Upvotes: 0

Views: 893

Answers (1)

R2D2
R2D2

Reputation: 10737

Issue clarified , in the extended json format supported by mongoDB the date field in shell mode ( not strict mode ) can support dates outside the range 0000-9999 and it is understood by mongodb like signed 64bit integer in the form:

 mongos> new Date(1313124124122341)
 ISODate("43581-03-31T21:08:42.341Z")
 mongos> 
 mongos> new Date(-121213232233222)
 ISODate("-1872-11-27T01:42:46.778Z")
 mongos>

In Strict mode, is an ISO-8601 date format with a mandatory time zone field following the template YYYY-MM-DDTHH:mm:ss.mmm<+/-Offset>.

In Shell mode, is the JSON representation of a 64-bit signed integer giving the number of milliseconds since epoch UTC.

Strict Mode:

 { "$date": "<date>" }

mongo Shell Mode:

 new Date ( <date> )

https://docs.mongodb.com/manual/reference/mongodb-extended-json-v1/

Internally, Date objects are stored as a signed 64-bit integer representing the number of milliseconds since the Unix epoch (Jan 1, 1970).

Not all database operations and drivers support the full 64-bit range. You may safely work with dates with years within the inclusive range 0 through 9999.

So thanks alot @Alex & @Wernfried to provide the hints !

Upvotes: 2

Related Questions