[SERVER-4434] aggregation: add strptime functionality to parse string dates into ISODates Created: 06/Dec/11  Updated: 06/Dec/22  Resolved: 16/Mar/18

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Major - P3
Reporter: Daniel Pasette (Inactive) Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 13
Labels: expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-32771 Add format specifier for $dateFromStr... Closed
is duplicated by SERVER-23903 Add $date aggregation operators to co... Closed
is duplicated by SERVER-16418 Add a way to convert a number or stri... Closed
Related
is related to SERVER-447 new aggregation framework Closed
is related to SERVER-29208 Add the $dateFromString A/F operator ... Closed
is related to SERVER-29209 Add TZ support to the new $dateFromSt... Closed
Assigned Teams:
Query
Participants:

 Description   

Example:
{$date: "2016-04-16T13:32:10"}
would return the Date object ISODate("2016-04-16T13:32:10.000Z")



 Comments   
Comment by David Storch [ 16/Mar/18 ]

Marking as a duplicate of SERVER-32771, the ticket under which this functionality was implemented.

Comment by Asya Kamsky [ 15/Mar/18 ]

Now that $dateFromString takes an optional "format" argument, you can specify custom format for parsing any string into a date. Example:

// Input:
{_id: 0, value: "12 25 2017"}
// Pipeline:
db.coll.aggregate({[
  $project: {
    date: {
      $dateFromString: {
        dateString: "$value",
        format: "%m %d %Y",
        timezone: “America/New_York”
      }
    }
  }
]})
// Output:
{_id: 0, date: ISODate("2017-12-25T05:00:00Z")}

In addition, you can optionally specify "onError" and "onNull" parameters which provide the value to use if null (or empty) input is provided, and if an error is encountered when attempting to convert the string to date. Example syntax would be:

// Input:
{_id: 0, value: "12 25 2017"}
{_id: 1, value: "25 12 2017"}
{_id: 2}
// Pipeline:
db.coll.aggregate({[
  $project: {
    date: {
      $dateFromString: {
        dateString: "$value",
        format: "%m %d %Y",
        onNull: ISODate("2017-01-01T00:00:00Z"),
        onError: {$concat:[ "Could not convert string '", "$value", "' to Date"]},
        timezone: “America/New_York”
      }
    }
  }
]})
// Output:
{_id: 0, date: ISODate("2017-12-25T05:00:00Z")}
{_id: 1, date:  "Could not convert string '25 12 2017' to Date"
{_id: 2, date: ISODate("2017-01-01T00:00:00Z")}

Comment by Asya Kamsky [ 14/Mar/18 ]

Now that $dateFromString takes a format string, I believe this can be closed.

See SERVER-32771 and SERVER-32904 which added this functionality.

Comment by David Storch [ 04/Oct/17 ]

An update on this ticket: We have implemented a new aggregation framework operator called $dateFromString under SERVER-29208, first available in development release 3.5.11. (We subsequently added support for parsing a date string in a particular timezone under SERVER-29209, also released in 3.5.11). This allows most strings in ISO 8601 format to be parsed to UTC date times. Here is an example where we parse a date in YYYY-MM-DDThh:mm:ss.sssZ format:

> db.c.aggregate([{$project: {time: {$dateFromString: {dateString: "2017-10-04T20:05:34.659Z"}}}}])
{ "_id" : ObjectId("59d53c526bce21f94e32a6f9"), "time" : ISODate("2017-10-04T20:05:34.659Z") }

We can also parse ISO 8601 variations such as those that omit the time of day, or which omit dashes or colons:

{ "_id" : ObjectId("59d53c526bce21f94e32a6f9"), "time" : ISODate("2017-10-04T00:00:00Z") }
> db.c.aggregate([{$project: {time: {$dateFromString: {dateString: "20171004T200534.659Z"}}}}]);
> db.c.aggregate([{$project: {time: {$dateFromString: {dateString: "20171004"}}}}]);
{ "_id" : ObjectId("59d53c526bce21f94e32a6f9"), "time" : ISODate("2017-10-04T00:00:00Z") }

Lastly, we can handle ISO 8601 week-numbered dates using formats like YYYY-Www-DThh:mm:ss.sssZ:

> db.c.aggregate([{$project: {time: {$dateFromString: {dateString: "2017-W01-4T20:05:34.659Z"}}}}]);
{ "_id" : ObjectId("59d53c526bce21f94e32a6f9"), "time" : ISODate("2017-01-05T20:05:34.659Z") }
> db.c.aggregate([{$project: {time: {$dateFromString: {dateString: "2016W527"}}}}]);
{ "_id" : ObjectId("59d53c526bce21f94e32a6f9"), "time" : ISODate("2017-01-01T00:00:00Z") }

I can't currently claim full ISO 8601 compliance, but I believe that we can correctly handle any valid ISO 8601 date string in the years 0000-9999.

I'm leaving this ticket open to track the remaining strptime()-like functionality in which the user can specify the expected format of the date via a format string. This would allow applications to use the aggregation framework to easily parse non-standard date formats to UTC datetimes (milliseconds since the Unix epoch).

Comment by Gabriel Zimmermann [ 21/Feb/17 ]

Any ETA on this? I need this feature for my aggregation pipeline. I can group by date, but I need to calculate the offset of a document until the end of the `$group` and this is the only way I can think of.

Comment by Andy Schwerin [ 02/May/16 ]

The example from the description:

Example:
{$date: "2016-04-16T13:32:10"}
would return the Date object ISODate("2016-04-16T13:32:10.000Z")

suggests that the parser will presume that "no timezone provided" equals "UTC timezone". I suggest instead that we should issue an error if the user fails to provide a timezone.

Comment by Eliot Horowitz (Inactive) [ 20/Apr/12 ]

The latter, a new function called $stptime

Comment by Annie Chin [ 20/Apr/12 ]

I wanted to try implementing this ticket,
but I'm a little confused about what it means. Is the point to allow
ISODate to take in a string OR its current parameters (and then parse
the string and etc.)? Or is it to add a new expression called
$strptime that takes in a string, parses the elements out, and then
passes those elements to $ISODate to produce an ISODate?

Generated at Thu Feb 08 03:05:58 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.