Navigation

$dateToString (aggregation)

$dateToString

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

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.

Changed in version 4.0: The format field is optional if featureCompatibilityVersion (fCV) is set to "4.0" or greater. For more information on fCV, see setFeatureCompatibilityVersion.

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.

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"

New in version 3.6.

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.

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

Tip
See also:

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
%

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"
}
Give Feedback