$dateToString (aggregation)

$dateToString (aggregation)

Definition

$dateToString

New in version 3.0.

Converts a date object to a string according to a user-specifiedformat.

The $dateToString expression has the followingoperator expression syntax:

{ $dateToString: {
    date: <dateExpression>,
    format: <formatString>,
    timezone: <tzExpression>,
    onNull: <expression>
} }

The $dateToString takes a document with the following fields:

FieldDescriptiondate

Changed in version 3.6.

The date to convert to string. <dateExpression> must be avalid expression thatresolves to a Date, aTimestamp, or anObjectID.formatOptional. The date format specification. <formatString> can be anystring literal, containing 0 or more format specifiers. For alist of specifiers available, see Format Specifiers.

If unspecified, $dateToString uses"%Y-%m-%dT%H:%M:%S.%LZ" as the default format.

Changed in version 4.0: The format field is optional iffeatureCompatibilityVersion (fCV) is set to "4.0" orgreater. For more information on fCV, seesetFeatureCompatibilityVersion.

timezoneOptional. The timezone of the operation result.<tzExpression> must be a valid expression that resolves to a string formatted as eitheran Olson Timezone Identifier or aUTC Offset.If no timezone is provided, the result is displayed in UTC.

FormatExamplesOlson Timezone Identifier

"America/New_York"
"Europe/London"
"GMT"

UTC Offset

+/-[hh]:[mm], e.g. "+04:45"
+/-[hh][mm], e.g. "-0530"
+/-[hh], e.g. "+03"

New in version 3.6.

onNullOptional. The value to return if the date is null or missing.The arguments can be any valid expression.

If unspecified, $dateToString returns null if thedate is null or missing.

New in version 4.0: Requires featureCompatibilityVersion (fCV) set to"4.0" or greater. For more information on fCV, seesetFeatureCompatibilityVersion.

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 formatNew in version 3.4.	`0000`-`9999`
`%H`	Hour (2 digits, zero padded, 24-hour clock)	`00`-`23`
`%j`	Day of year (3 digits, zero padded)	`001`-`366`
`%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`
`%w`	Day of week (1-Sunday, 7-Saturday)	`1`-`7`
`%u`	Day of week number in ISO 8601 format (1-Monday, 7-Sunday)New in version 3.4.	`1`-`7`
`%U`	Week of year (2 digits, zero padded)	`00`-`53`
`%V`	Week of Year in ISO 8601 formatNew in version 3.4.	`01`-`53`
`%Y`	Year (4 digits, zero padded)	`0000`-`9999`
`%z`	The timezone offset from UTC.New in version 3.6.	`+/-[hh][mm]`
`%Z`	The minutes offset from UTC as a number. For example, if thetimezone offset (`+/-[hhmm]`) was `+0445`, the minutesoffset is `+285`.New in version 3.6.	`+/-mmm`
`%%`	Percent Character as a Literal	`%`

Example

Consider a sales collection with the following document:

{
  "_id" : 1,
  "item" : "abc",
  "price" : 10,
  "quantity" : 2,
  "date" : ISODate("2014-01-01T08:15:39.736Z")
}

The following aggregation uses $dateToString toreturn the date field as formatted strings:

db.sales.aggregate(
   [
     {
       $project: {
          yearMonthDayUTC: { $dateToString: { format: "%Y-%m-%d", date: "$date" } },
          timewithOffsetNY: { $dateToString: { format: "%H:%M:%S:%L%z", date: "$date", timezone: "America/New_York"} },
          timewithOffset430: { $dateToString: { format: "%H:%M:%S:%L%z", date: "$date", timezone: "+04:30" } },
          minutesOffsetNY: { $dateToString: { format: "%Z", date: "$date", timezone: "America/New_York" } },
          minutesOffset430: { $dateToString: { format: "%Z", date: "$date", timezone: "+04:30" } }
       }
     }
   ]
)

The operation returns the following result:

{
   "_id" : 1,
   "yearMonthDayUTC" : "2014-01-01",
   "timewithOffsetNY" : "03:15:39:736-0500",
   "timewithOffset430" : "12:45:39:736+0430",
   "minutesOffsetNY" : "-300",
   "minutesOffset430" : "270"
}