Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-76696

$dateToString aggregation with timezone outputs invalid ISO8601 string

XMLWordPrintableJSON

    • Query Execution
    • Minor Change
    • ALL
    • Hide

      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'

      Show
      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'
    • 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

            Assignee:
            kevin.cherkauer@mongodb.com Kevin Cherkauer
            Reporter:
            danieljewell@gmail.com Daniel Jewell
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: