$dateToString (aggregation)
On this page
Definition
$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:FieldDescriptiondate
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 notimezone
is provided, the result is displayed inUTC
.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" 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 thedate
is null or missing.
Format Specifiers
The following format specifiers are available for use in the
<formatString>
:
Specifiers | Description | Possible Values |
---|---|---|
%b | Abbreviated month (3 letters) | jan -dec |
%B | Full month name | january -december |
%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 |
%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 |
%u | Day of week number in ISO 8601 format (1-Monday, 7-Sunday) | 1 -7 |
%U | Week of year (2 digits, zero padded) | 00 -53 |
%V | Week of Year in ISO 8601 format | 01 -53 |
%w | Day of week (0-Sunday, 6-Saturday) | 0 -6 |
%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 | % |
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" }