$dateFromString (aggregation)

On this page

Definition

Behavior
Format Specifiers
Examples

Definition

$dateFromString

New in version 3.6.

Converts a date/time string to a date object.

The $dateFromString expression has the following syntax:

{ $dateFromString: {
     dateString: <dateStringExpression>,
     format: <formatStringExpression>,
     timezone: <tzExpression>,
     onError: <onErrorExpression>,
     onNull: <onNullExpression>
} }

The $dateFromString takes a document with the following fields:

Field	Description
`dateString`	The date/time string to convert to a date object. See Date for more information on date/time formats. If specifying the `timezone` option to the operator, do not include time zone information in the `dateString`.
`format`	Optional. The date format specification of the `dateString`. The `format` can be any expression that evaluates to a string literal, containing 0 or more format specifiers. For a list of specifiers available, see Format Specifiers. If unspecified, `$dateFromString` uses `"%Y-%m-%dT%H:%M:%S.%LZ"` as the default format but accepts a variety of formats and attempts to parse the `dateString` if possible.
`timezone`	Optional. The time zone to use to format the date. If the `dateString` argument is formatted like '2017-02-08T12:10:40.787Z', in which the 'Z' at the end indicates Zulu time (UTC time zone), you cannot specify the `timezone` argument. `<timezone>` allows for the following options and expressions that evaluate to them: an Olson Timezone Identifier, such as `"Europe/London"` or `"America/New_York"`, or a UTC offset in the form: `+/-[hh]:[mm]`, e.g. `"+04:45"`, or `+/-[hh][mm]`, e.g. `"-0530"`, or `+/-[hh]`, e.g. `"+03"`, or The strings `"Z"`, `"UTC"`, or `"GMT"` For more information on expressions, see Expressions.
`onError`	Optional. If `$dateFromString` encounters an error while parsing the given `dateString`, it outputs the result value of the provided `onError` expression. This result value can be of any type. If you do not specify `onError`, `$dateFromString` throws an error if it cannot parse `dateString`.
`onNull`	Optional. If the `dateString` provided to `$dateFromString` is `null` or missing, it outputs the result value of the provided `onNull` expression. This result value can be of any type. If you do not specify `onNull` and `dateString` is `null` or missing, then `$dateFromString` outputs `null`.

Tip

Behavior

Example

Results

{ $dateFromString: {
    dateString: "2017-02-08T12:10:40.787"
} }

ISODate("2017-02-08T12:10:40.787Z")

{ $dateFromString: {
     dateString: "2017-02-08T12:10:40.787",
     timezone: "America/New_York"
} }

ISODate("2017-02-08T17:10:40.787Z")

{ $dateFromString: {
     dateString: "2017-02-08"
} }

ISODate("2017-02-08T00:00:00Z")

{ $dateFromString: {
     dateString: "06-15-2018",
     format: "%m-%d-%Y"
} }

ISODate("2018-06-15T00:00:00Z")

{ $dateFromString: {
     dateString: "15-06-2018",
     format: "%d-%m-%Y"
} }

ISODate("2018-06-15T00:00:00Z")

{ $dateFromString: {
     dateString: "WED jan 31 12:05:28 +03:30 1996"
} }

ISODate("1996-01-31T08:35:28.000Z")

Format Specifiers

The following format specifiers are available for use in the <formatString>:

Specifiers	Description	Possible Values
`%d`	Day of Month (2 digits, zero padded)	`01`-`31`
`%G`	Year in ISO 8601 format	`0000`-`9999`
`%H`	Hour (2 digits, zero padded, 24-hour clock)	`00`-`23`
`%L`	Millisecond (3 digits, zero padded)	`000`-`999`
`%m`	Month (2 digits, zero padded)	`01`-`12`
`%M`	Minute (2 digits, zero padded)	`00`-`59`
`%S`	Second (2 digits, zero padded)	`00`-`60`
`%u`	Day of week number in ISO 8601 format (1-Monday, 7-Sunday)	`1`-`7`
`%V`	Week of Year in ISO 8601 format	`1`-`53`
`%Y`	Year (4 digits, zero padded)	`0000`-`9999`
`%z`	The timezone offset from UTC.	`+/-[hh][mm]`
`%Z`	The minutes offset from UTC as a number. For example, if the timezone offset (`+/-[hhmm]`) was `+0445`, the minutes offset is `+285`.	`+/-mmm`
`%%`	Percent Character as a Literal	`%`

Examples

Converting Dates

Consider a collection logmessages that contains the following documents with dates.

{ _id: 1, date: "2017-02-08T12:10:40.787", timezone: "America/New_York", message:  "Step 1: Started" },
{ _id: 2, date: "2017-02-08", timezone: "-05:00", message:  "Step 1: Ended" },
{ _id: 3, message:  " Step 1: Ended " },
{ _id: 4, date: "2017-02-09", timezone: "Europe/London", message: "Step 2: Started"},
{ _id: 5, date: "2017-02-09T03:35:02.055", timezone: "+0530", message: "Step 2: In Progress"}

The following aggregation uses $dateFromString to convert the date value to a date object:

db.logmessages.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: 'America/New_York'
         }
      }
   }
} ] )

The above aggregation returns the following documents and converts each date field to the Eastern Time Zone:

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : ISODate("2017-02-08T05:00:00Z") }
{ "_id" : 3, "date" : null }
{ "_id" : 4, "date" : ISODate("2017-02-09T05:00:00Z") }
{ "_id" : 5, "date" : ISODate("2017-02-09T08:35:02.055Z") }

The timezone argument can also be provided through a document field instead of a hard coded argument. For example:

db.logmessages.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: '$timezone'
         }
      }
   }
} ] )

The above aggregation returns the following documents and converts each date field to their respective UTC representations.

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : ISODate("2017-02-08T05:00:00Z") }
{ "_id" : 3, "date" : null }
{ "_id" : 4, "date" : ISODate("2017-02-09T00:00:00Z") }
{ "_id" : 5, "date" : ISODate("2017-02-08T22:05:02.055Z") }

`onError`

If your collection contains documents with unparsable date strings, $dateFromString throws an error unless you provide an aggregation expression to the optional onError parameter.

For example, given a collection dates with the following documents:

{ "_id" : 1, "date" : "2017-02-08T12:10:40.787", timezone: "America/New_York" },
{ "_id" : 2, "date" : "20177-02-09T03:35:02.055", timezone: "America/New_York" }

You can use the onError parameter to return the invalid date in its original string form:

db.dates.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: '$timezone',
            onError: '$date'
         }
      }
   }
} ] )

This returns the following documents:

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : "20177-02-09T03:35:02.055" }

`onNull`

If your collection contains documents with null date strings, $dateFromString returns null unless you provide an aggregation expression to the optional onNull parameter.

For example, given a collection dates with the following documents:

{ "_id" : 1, "date" : "2017-02-08T12:10:40.787", timezone: "America/New_York" },
{ "_id" : 2, "date" : null, timezone: "America/New_York" }

You can use the onNull parameter to have $dateFromString return a date representing the unix epoch instead of null:

db.dates.aggregate( [ {
   $project: {
      date: {
         $dateFromString: {
            dateString: '$date',
            timezone: '$timezone',
            onNull: new Date(0)
         }
      }
   }
} ] )

This returns the following documents:

{ "_id" : 1, "date" : ISODate("2017-02-08T17:10:40.787Z") }
{ "_id" : 2, "date" : ISODate("1970-01-01T00:00:00Z") }

Back

$dateFromParts

$dateSubtract