[COMPASS-6785] Investigate changes in SERVER-76696: $dateToString aggregation with timezone outputs invalid ISO8601 string Created: 02/May/23  Updated: 22/Jun/23  Resolved: 09/May/23

Status: Closed
Project: Compass
Component/s: None
Affects Version/s: None
Fix Version/s: No version

Type: Investigation Priority: Major - P3
Reporter: Backlog - Core Eng Program Management Team Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-76696 $dateToString aggregation with timezo... Closed
Documentation Changes: Not Needed

 Description   
Original Downstream Change Summary

The current default behavior of MongoDB's $dateToString aggregation expression is incorrect because it violates ISO 8601. (It tacks on a "Z" to the end of the string by default, which implies that it is in UTC [1], but in actually it's in the timezone of the local server.)

This ticket proposes a number of routes to change the default behavior of $dateToString to bring it into compliance with ISO 8601.

[1] https://en.wikipedia.org/wiki/ISO_8601#Coordinated_Universal_Time_(UTC)

Description of Linked Ticket

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 PM Bot [ 22/Jun/23 ]

Fix Version updated for upstream SERVER-76696:
7.1.0-rc0

Comment by Rhys Howell [ 09/May/23 ]

No devtools impact, we aren't doing anything with the dates or the `$dateToString` stage directly.

Generated at Wed Feb 07 22:44:20 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.