[SERVER-76696] $dateToString aggregation with timezone outputs invalid ISO8601 string Created: 28/Apr/23  Updated: 29/Oct/23  Resolved: 22/Jun/23

Status: Closed
Project: Core Server
Component/s: Aggregation Framework, Query Language, Querying
Affects Version/s: 6.0.3
Fix Version/s: 7.1.0-rc0

Type: Bug Priority: Major - P3
Reporter: Daniel Jewell Assignee: Kevin Cherkauer
Resolution: Fixed Votes: 0
Labels: DateTime, TimeZone, aggregation, aggregation-framework, conversion, date, dateToString, timezone
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

MongoDB Community v6.0.3 / git revision f803681c3ae19817d31958965850193de067c516 (ubuntu 20.04)


Issue Links:
Depends
is depended on by TOOLS-3294 Investigate changes in SERVER-76696: ... Closed
is depended on by COMPASS-6785 Investigate changes in SERVER-76696: ... Closed
Documented
is documented by DOCS-16090 [SERVER] $dateToString outputs invali... Closed
Related
related to SERVER-28610 Add time zone support, and %z and %Z ... Closed
related to SERVER-33173 Make "format" parameter optional for ... Closed
is related to SERVER-49979 Support expression $dateToString in SBE Closed
Assigned Teams:
Query Execution
Backwards Compatibility: Minor Change
Operating System: ALL
Steps To Reproduce:

From the mongo shell:

 
db.createCollection("datetest")
 
db.datetest.insertOne({"name": "Hello", "transactionDateTS": ISODate()})
 
db.datetest.aggregate([{ "$addFields": {"txString": { "$dateToString": {date: "$transactionDateTS", timezone: "America/New_York"} } }} ])

the output will vary as the code uses the current time, but an example follows:

[ ...
transactionDateTS: ISODate("2023-04-28T20:52:40.752Z"),
txString: '2023-04-28T16:52:40.752Z'
...]

In this case, transactionDateTS != txString

Expected output is either:

(less preferable)
txString: '2023-04-28T16:52:40.752'

(more preferable, because the UTC Offset should already be known from the date/time conversion... )
txString: '2023-04-28T16:52:40.752-04:00'

Sprint: QE 2023-06-12, QE 2023-06-26
Participants:

 Description   

When using the $dateToString aggregation operator on an ISODate value, specifying a timezone but not a format string, an invalid/inaccurate ISO8601 date string is returned.

Specifically: 2023-04-26T12:06:17.194Z when using something like:

db.collection.aggregate([{ "$addFields": {"txDateString": { "$dateToString": {date: "$transactionDateTS", timezone: "America/New_York"} } }} ])`

The root issue is that the "Z" categorically implies a timezone of UTC (+00:00)... But, in this case, that's absolutely incorrect and, technically, makes it an invalid ISO8601 date string. (I suppose one could argue that it's syntatically valid - but it's certainly misleading and could result in major issues if another program were to parse the date string as it would interpret the time as "Zulu"/UTC because of the "Z".) The inclusion of the "Z" when explicitly specifying a timezone is certainly unexpected...

At a minimum, when a format string is unspecified and a date/time in UTC is converted to another time zone, the "Z" should be left off. Ideally, the UTC offset (obtained from the timezone conversion) should be included as per ISO8601 – i.e. in this case, 2023-04-26T12:06:17.194-04:00 (at the time of this writing, America/New_York is on Eastern Daylight Time (EDT) which is UTC-04:00.)

Related issues appear to include: SERVER-33173 and SERVER-28610



 Comments   
Comment by Githook User [ 21/Jun/23 ]

Author:

{'name': 'Kevin Cherkauer', 'email': 'kevin.cherkauer@mongodb.com', 'username': 'kevin-cherkauer'}

Message: SERVER-76696 Default $dateToString format omit 'Z' for non-UTC timezones
Branch: master
https://github.com/mongodb/mongo/commit/bda6d2c12112cdba737dcd48c8b97790cd4db25f

Comment by Xiaochen Wu [ 18/May/23 ]

kyle.suarez@mongodb.com 's proposal sounds reasonable to me. Send this back to engineering team to proceed with it.

FYI kateryna.kamenieva@mongodb.com 

Comment by Kyle Suarez [ 02/May/23 ]

Thank you danieljewell@gmail.com for the report. After reviewing this and the ISO 8601 spec with the team it does seem completely wrong to be tacking on the "Z".

I'm tagging our Query Product team to discuss next steps for this ticket, as it does affect default behavior, which kateryna.kamenieva@mongodb.com flagged in our meeting. My suggestion would be to:

  • Take Daniel's suggestion and change the code to stop emitting the "Z" by default.
  • Do not backport this change to any of the existing released branches, so as not to break anyone depending on the existing format, but document that there is a change in behavior starting with the next release.

Lastly, I've marked this ticket as a "minor change" and tagged the downstream changes teams since this may affect them if they use this expression.

Comment by Daniel Jewell [ 28/Apr/23 ]

One thought ...

Yes, the current documentation for $dateToString does reference a default value for format of "%Y-%m-%dT%H:%M:%S.%LZ" (see: https://www.mongodb.com/docs/manual/reference/operator/aggregation/dateToString/#std-label-format-specifiers)

That said, I suppose the issue could be viewed as more of an issue with the default format string (namely, always including a string constant of "Z" - implying a timezone-aware value that is UTC... when in fact that's not necessarily the case... )

Generated at Thu Feb 08 06:33:21 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.