$dateToString (aggregation)

On this page

Definition

Compatibility
Syntax
Format Specifiers
Example

This version of the documentation is archived and no longer supported. View the current documentation to learn how to upgrade your version of MongoDB.

Definition

$dateToString: Converts a date object to a string according to a user-specified format.

Compatibility

You can use $dateToString for deployments hosted in the following environments:

MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

MongoDB Enterprise: The subscription-based, self-managed version of MongoDB
MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB

Syntax

The $dateToString expression has the following operator expression syntax:

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

The $dateToString takes a document with the following fields:

Field

Description

date

Changed in version 3.6.

The date to convert to string. <dateExpression> must be a valid expression that resolves to a Date, a Timestamp, or an ObjectID.

format

Optional. The date format specification. <formatString> can be any string literal, containing 0 or more format specifiers. For a list of specifiers available, see Format Specifiers.

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

timezone

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

Note

Etc/ Timezones

Etc/* timezones are supported starting in MongoDB 5.0.4. In earlier versions of MongoDB, use alternative timezone designations. For example, use UTC instead of Etc/UTC.

Format

Examples

Olson 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"

onNull

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

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

Tip

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 New 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 format New 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 the timezone offset (`+/-[hhmm]`) was `+0445`, the minutes offset 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 to return 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"
}

Back

$dateFromString (aggregation)

$dayOfMonth (aggregation)