New Date/Time Support in MongoDB

London, UK

Updated on Friday, October 13th 2017: Reflects changes made since MongoDB 3.5.12 — this now documents the state as of version MongoDB 3.6.0-rc0.

In the past few months I have been working on adding time zone support to MongoDB's Aggregation Framework. This support brings in the timelib library that is also used in PHP and HHVM to do time zone calculations.

Time Zone Support for Date Extraction Operators MongoDB's Aggregation Framework already has several operators to extract date/time information from a Date value. For example, $month would retrieve the month from a Date value. Considering we have the document: { _id: 'derickr', dateOfBirth: ISODate("1978-12-22T08:15:00Z") } The following PHP code would extract the month 12 from the dateOfBirth field: <?php include 'vendor/autoload.php'; $collection = (new MongoDB\Client)->demo->birthdays; $cursor = $collection->aggregate([ [ '$project' => [ 'birthMonth' => [ '$month' => '$dateOfBirth' ] ] ] ]); foreach ($cursor as $person) { echo $person->birthMonth; } However, it was not possible to extract this information in the local time zone. So although my birth-time is given at 08:15:00, in reality it was 09:15 in the Netherlands. The new functionality adds the timezone argument to the date extraction functions. Currently the operators only take a single value: { "$month" : ISODateValue } Because it is not possible to add another argument to this, the syntax has been extended to: { "$month" : { "date" : ISODateValue } } And then with the time zone argument added, it becomes: { "$month" : { "date" : ISODateValue, "timezone": timeZoneIdentifier } } For example: { $hour: { date: ISODate("1978-12-22T08:15:00Z"), timezone: "Europe/Amsterdam" } } Would return 09 (One hour later than UTC). The timezone argument is optional, and must evaluate to either an Olson Time Zone Identifier such as Europe/London or America/New_York , or, an UTC offset string in the forms: +03 , -0530 , and +04:45 . If you specify a timezone argument it means that the dateString that you provided will be interpreted as it was in that time zone. In PHP, this example extracts the hour, and expresses the value in the Europe/Amsterdam time zone: <?php include 'vendor/autoload.php'; $collection = (new MongoDB\Client)->demo->birthdays; $cursor = $collection->aggregate([ [ '$project' => [ 'birthHour' => [ '$hour' => [ "date" => '$dateOfBirth', "timezone" => "Europe/Amsterdam", ] ] ] ] ]); foreach ($cursor as $person) { echo $person->birthHour, "

"; }

The $dateToParts Operator Extracting a single value from a date can easily be done with one of the Date Extraction operators, but it becomes cumbersome to do this for all the fields one by one, especially when considering the application of time zones as well: { "$project" : { "year": { "$year" : { "date" : '$dob', "timezone" : "Europe/Amsterdam" } }, "month": { "$month" : { "date" : '$dob', "timezone" : "Europe/Amsterdam" } }, "day": { "$day" : { "date" : '$dob', "timezone" : "Europe/Amsterdam" } }, "hour": { "$hour" : { "date" : '$dob', "timezone" : "Europe/Amsterdam" } }, "minute": { "$minute" : { "date" : '$dob', "timezone" : "Europe/Amsterdam" } }, } } The new $dateToParts operator simplifies having multiple single date value extraction operators into a single one. Its syntax is: { "$project" : { "parts" : { "$dateToParts" : { "date" : ISODateValue, "timezone" : timeZoneIdentifier, "iso8601" : boolean } } } } The timezone argument is optional, and is interpreted in the same as the timezone argument in the Date Extraction functions as explained above. The result of the operator is a sub-document with the broken down parts, expressed in the (optionally) given time zone: "parts" : { "year" : 1978, "month" : 12, "day" : 22, "hour" : 9, "minute" : 15, "second" : 0, "millisecond" : 0 } $dateToParts also supports a third boolean argument, iso8601 . If set to true , instead of year , month , and day , it returns the ISO 8601 isoWeekYear , isoWeek , and isoDayOfWeek fields representing an ISO Week Date. With the same date, the example is represented as: "parts" : { "isoWeekYear" : 1978, "isoWeek" : 51, "isoDayOfWeek" : 5, "hour" : 9, "minute" : 15, "second" : 0, "millisecond" : 0 } In PHP: <?php include 'vendor/autoload.php'; $collection = (new MongoDB\Client)->demo->birthdays; $cursor = $collection->aggregate([ [ '$project' => [ 'parts' => [ '$dateToParts' => [ "date" => '$dateOfBirth', "timezone" => "Europe/Amsterdam", ] ] ] ] ]); foreach ($cursor as $person) { var_dump( $person->parts ); } Which outputs, with formatting: class MongoDB\Model\BSONDocument#5 (1) { private $storage => array(7) { 'year' => int(1978) 'month' => int(12) 'day' => int(22) 'hour' => int(9) 'minute' => int(15) 'second' => int(0) 'millisecond' => int(0) } }

The $dateFromParts Operator The new $dateFromParts operator does the opposite of the $dateToParts operator and constructs a new Date value from its constituent parts, with the possibility of interpreting the given values in a different time zone. Its syntax is either: { "$project" : { "date" : { "$dateFromParts": { "year" : yearExpression, "month" : monthExpression, "day" : dayExpression, "hour" : hourExpression, "minute" : minuteExpression, "second" : secondExpression, "millisecond" : millisecondExpression, "timezone" : timezoneExpression } } } } or: { "$project" : { "date" : { "$dateFromParts": { "isoWeekYear" : isoWeekYearExpression, "isoWeek" : isoWeekExpression, "isoDayOfWeek" : isoDayOfWeekExpression, "hour" : hourExpression, "minute" : minuteExpression, "second" : secondExpression, "millisecond" : millisecondExpression, "timezone" : timezoneExpression } } } } Each argument's expression needs to evaluate to a number. This means the source can be either double, NumberInt, NumberLong, or Decimal. Decimal and double values are only supported if they convert to a NumberLong without any data loss. Every argument is optional, except for year or isoWeekYear , depending on which variant is used. If month , day , isoWeek , or isoDayOfWeek are not given, they default to 1 . The hour , minute , second and millisecond values default to 0 if not present. The timezone argument is interpreted in the same as the timezone argument in the Date Extraction functions as explained above. In PHP, an example looks like: <?php include 'vendor/autoload.php'; $collection = (new MongoDB\Client)->demo->birthdays; $cursor = $collection->aggregate([ [ '$project' => [ 'date' => [ '$dateFromParts' => [ "year" => 1978, "month" => 12, "day" => 22, "hour" => 9, "minute" => 15, "second" => 0, "millisecond" => 0, "timezone" => "Europe/Amsterdam", ] ] ] ] ]); foreach ($cursor as $person) { var_dump( $person->date->toDateTime() ); } Which outputs: class DateTime#12 (3) { public $date => string(26) "1978-12-22 08:15:00.000000" public $timezone_type => int(1) public $timezone => string(6) "+00:00" }

Changes to the $dateToString Operator The $dateToString operator is extended with the timezone argument. Its full new syntax is now: { $dateToString: { format: formatString, date: dateExpression, timezone: timeZoneIdentifier } } The timezone argument is optional. If present, it formats the string according to the given time zone, otherwise it uses UTC. The $dateToString format arguments have also been expanded. With the addition of the timezone argument came the %z and %Z format specifiers: %z The +hhmm or -hhmm numeric time zone as a string (that is, the hour and minute offset from UTC). Example: +0445 , -0500 %Z The minutes offset from UTC as a number. Example (following the +0445 and -0500 from %z ): +285 , -300 Once SERVER-29627 gets merged, the following new format specifiers will also be available: %a The abbreviated English name of the day of the week. %b The abbreviated English name of the month. %e The day of the month as a decimal number, but unlike %d , pre-padded with space instead of a 0 . An example of this in PHP: <?php include 'vendor/autoload.php'; $collection = (new MongoDB\Client)->demo->birthdays; $cursor = $collection->aggregate([ [ '$project' => [ 'date' => [ '$dateToString' => [ 'date' => '$dateOfBirth', 'format' => '%Y-%m-%d %H:%M:%S %z', 'timezone' => 'Australia/Sydney', ] ] ] ] ]); foreach ($cursor as $person) { echo $person->date; } Which outputs: 1978-12-22 19:15:00 +1100

The $dateFromString Operator Analogous to PHP's DateTimeImmutable constructor, this operator can be used to create a Date value out of a string. It has the following syntax: { "$dateFromString": { "dateString": dateString, "timezone": timeZoneIdentifier } } The dateString could be anything like: 2017-08-04T17:02:51Z

August 4, 2017 17:10:27.812+0100 In fact, it will accept everything that PHP's DateTimeImmutable constructor accepts as under the hood, it uses the same library. MongoDB enforces though that it is an absolute date/time string. The timezone argument is optional, and is interpreted in the same as the timezone argument in the Date Extraction functions as explained above. For example: { $dateFromString: { dateString: "2017-08-04T17:06:41.113", timezone: "Europe/London" } } Would mean 17:06 local time in London, or 16:06 in UTC (as London right now is at UTC+1). It is not allowed to specify a time zone through the dateString (such as the ending Z or +0400 ) and also specify a time zone through the timezone argument. In that case, an exception is thrown. In PHP, this looks like: <?php include 'vendor/autoload.php'; $collection = (new MongoDB\Client)->demo->birthdays; $cursor = $collection->aggregate([ [ '$project' => [ 'date' => [ '$dateFromString' => [ "dateString" => 'August 8th, 2017. 14:14:40', "timezone" => "Europe/Amsterdam", ] ] ] ] ]); foreach ($cursor as $person) { var_dump( $person->date->toDateTime() ); } Which outputs: class DateTime#12 (3) { public $date => string(26) "2017-08-08 12:14:40.000000" public $timezone_type => int(1) public $timezone => string(6) "+00:00" } As you can see, the time zone information is lost when the data is transferred between MongoDB and PHP as the BSON DateTime data type does not carry this information.

Using Date Expressions in $match From MongoDB 3.5.12, it is also possible to use the new date expressions (and other expressions) in the $match pipeline operator. For example, in order to find all the documents before June 17th, 2017 in the New York time zone: db.dates.aggregate( [ { $match: { date: { $gte: { $expr: { $dateFromString: { dateString: "June 17th, 2017", timezone: "America/New_York" } } } } } } ] ); Or from PHP: <?php include 'vendor/autoload.php'; $collection = (new MongoDB\Client)->demo->dates; $date = "June 17th, 2017"; $cursor = $collection->aggregate( [ [ '$match' => [ 'date' => [ '$gte' => [ '$expr' => [ '$dateFromString' => [ "dateString" => [ '$literal' => $date ], "timezone" => "America/New_York", ] ] ] ] ] ] ]); foreach ($cursor as $person) { var_dump( $person->date->toDateTime() ); } Please note the use of the $literal operator here, which should be used for any user input that might be able to sneak in an expression into the value.