Uploaded image for project: 'Documentation'
  1. Documentation
  2. DOCS-15857

[SERVER] Investigate changes in SERVER-50336: $dateFromString support for additional format specifiers (such as "%j" for day of year)

      Original Downstream Change Summary

      $dateFromString now supports the %j format specifier, "Day of Year", which allows a user to parse a date from a string containing only the year and a number to indicate which day it is of that year.

      Example here: https://github.com/10gen/mongo/blob/33190227bdd2c21328afe7f41ace33bf5d64eeab/jstests/aggregation/expressions/date_from_string.js#L556

      Note that %j counts the days from 0-355, not 1-356.

      Description of Linked Ticket

      The $dateFromString Format Specifiers don't include the strftime %j modifier which is used to decode the day of the year as a decimal number (range 001 to 366).

      It appears that this modifier is understood as part of kDateToStringFormatMap from timelib's TIMELIB_FORMAT_DAY_OF_YEAR when converting from a date to a string, but not from a string to a date.

      For example:

      db.foo.drop()
      db.foo.insert({ _id: 1, d: "2020-194-14:24:45.463" });
      db.foo.aggregate( [ {
         $project: {
            date: {
               $dateFromString: {
                  dateString: "%d",
                  format: "%Y-%j-%H:%M:%S.%L"
               }
            }
         }
      } ] )
      // "errmsg" : "Failed to optimize pipeline :: caused by :: Invalid format character '%j' in format string",
      

      Note that for the above we were able to work around this limitation as follows:

      db.foo.drop()
      db.foo.insert({ _id: 1, d: "2020-194-14:24:45.463" });
      db.foo.aggregate([
      { $project: {
          date: {
              $let: {
                  vars: {
                      parts: { $split: [ "$d", "-" ] }
                  },
                  in: { 
                      $add: [ 
                          { $toDate: { $concat: [ 
                              { $arrayElemAt: [ "$$parts", 0 ] }, 
                              "-01-01T", 
                              { $arrayElemAt: [ "$$parts", 2 ] } ] 
                          } }, 
                          { $multiply: [ 
                              { $subtract: [ 
                                  { $toInt: { $arrayElemAt: [ "$$parts", 1 ] } }, 
                                  1 ] }, 86400000 ] }
                      ] 
                  } 
              }
          }
      }}
      ]);
      // { "_id" : 1, "date" : ISODate("2020-07-12T14:24:45.463Z") }
      

            Assignee:
            kanchana.sekhar@mongodb.com Kanchana Sekhar
            Reporter:
            backlog-server-pm Backlog - Core Eng Program Management Team
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved:
              1 year, 11 weeks, 3 days ago