[SERVER-23410] Improve or remove support for dates in string expressions Created: 29/Mar/16  Updated: 17/May/19  Resolved: 02/Oct/17

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 3.6.0-rc0

Type: Improvement Priority: Major - P3
Reporter: Charlie Swanson Assignee: Charlie Swanson
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
is documented by DOCS-10862 Docs for SERVER-23410: Improve or rem... Closed
Related
related to SERVER-11400 Need a type conversion mechanism to c... Closed
is related to SERVER-12970 agg functions which coerceToString do... Closed
Backwards Compatibility: Minor Change
Sprint: Query 2017-08-21, Query 2017-09-11, Query 2017-10-02, Query 2017-10-23
Participants:

 Description   

A couple string expressions (notably in $substr(Bytes) and $concat) support dates as inputs, but do not include the number of milliseconds:

> db.foo.insert({x: new Date()})
WriteResult({ "nInserted" : 1 })
> db.foo.find()
{ "_id" : ObjectId("56fae3003ef38a5ce210b230"), "x" : ISODate("2016-03-29T20:18:08.339Z") }
> db.foo.aggregate({$project: {x: {$substr: ["$x", 0, 200]}}})
{ "_id" : ObjectId("56fae3003ef38a5ce210b230"), "x" : "2016-03-29T20:18:08" }

We should either fix these places to include the number of milliseconds, or remove support for using these expressions with date types.

One possible way forward is to add a $toString expression (related to SERVER-11400), then remove support and require an explicit conversion instead.



 Comments   
Comment by Charlie Swanson [ 02/Oct/17 ]

The above commit changes the formatting of dates that are converted to strings. Currently this is only possible via 'coersion', when a date is supplied to one of the following string expressions:

  • $concat
  • $substr, $substrBytes, $substrCP
  • $strcasecmp
  • $toLower
  • $toUpper

Previously, a date coerced to a string would be in the format "2017-10-02T12:11:36" (Oct 2, 2017, 12:11 and 36 seconds). This change appends the number of milliseconds, and the letter 'Z' (signifying this is in the 'Zulu', or UTC time zone) to the end, so now that date might look like this: "2017-10-02T12:11:36.213Z"

Comment by Githook User [ 02/Oct/17 ]

Author:

{'email': 'charlie.swanson@mongodb.com', 'name': 'Charlie Swanson', 'username': 'cswanson310'}

Message: SERVER-23410 Include millis and 'Z' when converting agg dates to strings
Branch: master
https://github.com/mongodb/mongo/commit/ee6b784fecd5a8e8717a7a17a78a0d54535c5df7

Comment by Asya Kamsky [ 01/Aug/17 ]

Looks like the reason for that is https://github.com/mongodb/mongo/blob/master/src/mongo/db/pipeline/value.cpp#L602-L603 where we call getTimestamp().toStringPretty(); not sure that this is the best format for Timestamp.

Comment by Asya Kamsky [ 01/Aug/17 ]

Something I recently noticed: even though ISODate and Timestamp seem to have the same behavior when a date field is expected, when passed to $substr and coerced to string they behave very differently.

db.foo.find()
{ "_id" : ObjectId("5980ba61e7223b33695dd825"), "d" : ISODate("2017-08-01T17:29:05.408Z"), "ts" : Timestamp(1501608545, 1) }
db.foo.aggregate({$project:{
     dateToString:{$substr:["$d",0,22]},
     tsToString:{$substr:["$ts",0,22]}}})
{ "_id" : ObjectId("5980ba61e7223b33695dd825"), "dateToString" : "2017-08-01T17:29:05", "tsToString" : "Aug  1 10:29:05:1" }

The last ":0" in the ts to string is the counter field, so that's expected, what's unexpected is that the format of the date part is "Dec XX HH:MM:SS" rather than

Generated at Thu Feb 08 04:03:18 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.