-
Type: Bug
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: 6.0.3
-
Component/s: Aggregation Framework, Query Language, Querying
-
Environment:MongoDB Community v6.0.3 / git revision f803681c3ae19817d31958965850193de067c516 (ubuntu 20.04)
-
Query Execution
-
Minor Change
-
ALL
-
-
QE 2023-06-12, QE 2023-06-26
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
- is depended on by
-
COMPASS-6785 Investigate changes in SERVER-76696: $dateToString aggregation with timezone outputs invalid ISO8601 string
- Closed
-
TOOLS-3294 Investigate changes in SERVER-76696: $dateToString aggregation with timezone outputs invalid ISO8601 string
- Closed
- is related to
-
SERVER-49979 Support expression $dateToString in SBE
- Closed
- related to
-
SERVER-28610 Add time zone support, and %z and %Z arguments to the $dateToString operator
- Closed
-
SERVER-33173 Make "format" parameter optional for $dateToString
- Closed