[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: |
|
||||||||||||||||||||||||||||||||||||
| Assigned Teams: |
Query Execution
|
||||||||||||||||||||||||||||||||||||
| Backwards Compatibility: | Minor Change | ||||||||||||||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: | From the mongo shell:
the output will vary as the code uses the current time, but an example follows: [ ... In this case, transactionDateTS != txString Expected output is either: (less preferable) (more preferable, because the UTC Offset should already be known from the date/time conversion... ) |
||||||||||||||||||||||||||||||||||||
| 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:
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: |
| Comments |
| Comment by Githook User [ 21/Jun/23 ] |
|
Author: {'name': 'Kevin Cherkauer', 'email': 'kevin.cherkauer@mongodb.com', 'username': 'kevin-cherkauer'}Message: |
| 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. |
| 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:
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... ) |