Question When parsing string as dates in MongoDB what is recommended implementation?
I ask this because we have vendors sending us the string representation of a date in the format of "2017-01-01" and "2017/01/01". I would have thought that the parsing of those different strings would return the same result in MongoDB using the Date() and ISODate().
Given the following examples running through MongoChef against MongoDB 3.4
1. print(new Date("2016-01-01"))
2. print(new Date("2016/01/01"))
3. print(new ISODate("2016-01-01"))
4. print(new ISODate("2016/01/01"))
Produces the following Results
1. Thu Dec 31 2015 18:00:00 GMT-0600 (Central Standard Time)
2. Fri Jan 01 2016 00:00:00 GMT-0600 (Central Standard Time)
3. Thu Dec 31 2015 18:00:00 GMT-0600 (Central Standard Time)
4. 2017-01-06T10:34:01.814-0600 E QUERY [thread1] Error: invalid ISO date
This does not make any sense to me. Anyone that could explain this to me would be much appreciated.
The one that is acceptable for me is option #2 so I ensure that our dates are all separated by "/" and are using the new Date() constructor.
"2017-01-01" and "2017/01/01". To keep it short, first one, an ISO format, so it will parse to UTC time and second one, an Non-ISO format, will parse to time in local time zone.
Consider the output in Mongo shell. All times are shown in UTC time.
1.new Date("2016-01-01") ---- ISODate("2016-01-01T00:00:00Z")
2.new Date("2016/01/01") ---- ISODate("2016-01-01T06:00:00Z")
3.new ISODate("2016-01-01") ---- ISODate("2016-01-01T00:00:00Z")
4.new ISODate("2016/01/01") ----2017-01-06T11:14:56.862-0600 E QUERY [thread1] Error: invalid ISO date :
Mongo db saves all the dates in UTC time.
Mongo chef transforms all the above saved UTC date times values to local time zone (-06:00 offset), only for display purposes.
So the choice is between ISODate("2016-01-01T00:00:00Z") vs ISODate("2016-01-01T06:00:00Z").
Both are UTC datetimes. Looks like you're only interested in date part (with time part set to midnight), the option 2 (new Date("2016/01/01") ---- ISODate("2016-01-01T06:00:00Z")) would be your choice and all the comparsion queries will work just fine as long as you create the date the same way you did while saving.
Reference:
https://docs.mongodb.com/manual/reference/bson-types/#document-bson-type-date
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date