[DRIVERS-2536] $dateFromString support for additional format specifiers (such as "%j" for day of year) Created: 27/Jan/23  Updated: 08/Mar/23  Resolved: 08/Mar/23

Status: Closed
Project: Drivers
Component/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Backlog - Core Eng Program Management Team Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
depends on SERVER-50336 $dateFromString support for additiona... Closed
Related
Driver Changes: Builder Changes Needed
Server Compat: 6.3
Upstream Changes 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   
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") }



 Comments   
Comment by James Kovacs [ 08/Mar/23 ]

Given that this is simply a string-based format specifier, additional format specifiers will automatically be supported. JAVA-4889 notes that this is already implemented in Java. Support for date format specifiers will be implemented in CSHARP-4540 (related ticket is DRIVERS-2553).

Comment by James Kovacs [ 08/Mar/23 ]

According to JAVA-4889 this should already work in Java. We also have DRIVERS-2553 and the corresponding CSHARP-4540 to implement format specifier support in C#. Closing this ticket as a dup.

Comment by James Kovacs [ 08/Mar/23 ]

C# currently translates DateTime.Parse(string) to $dateFromString but does not currently provide any way to pass a format specifier of any sort. Implementation should be straightforward, but it will require a bit of work. I suspect the same is true for Java. ashni.mehta@mongodb.com and/or jeff.yemin@mongodb.com can confirm. I would suggest generating language tickets for C# and Java so we can schedule the work.

Comment by Ashni Mehta [ 08/Mar/23 ]

Update: I filed https://jira.mongodb.org/browse/JAVA-4863 to address this. That ticket is currently blocked on https://jira.mongodb.org/browse/SERVER-74002

Generated at Thu Feb 08 08:25:49 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.