$dateFromString support for additional format specifiers (such as "%j" for day of year)

XMLWordPrintableJSON

    • Query Execution
    • Fully Compatible
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      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:
            Jennifer Peshansky (Inactive)
            Reporter:
            Alex Bevilacqua
            Votes:
            2 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: