[DOCS-14145] Investigate changes in SERVER-10651: Aggregation expressions to add/subtract Years/Quarters/Months Created: 21/Jan/21  Updated: 13/Nov/23  Resolved: 02/Mar/21

Status: Closed
Project: Documentation
Component/s: manual, Server
Affects Version/s: None
Fix Version/s: 4.9.0, Server_Docs_20231030, Server_Docs_20231106, Server_Docs_20231105, Server_Docs_20231113

Type: Task Priority: Major - P3
Reporter: Backlog - Core Eng Program Management Team Assignee: Dave Cuthbert (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
documents SERVER-10651 Aggregation expressions to add/subtra... Closed
Participants:
Days since reply: 3 years, 2 weeks, 6 days ago
Epic Link: DOCSP-9747

 Description   

Description

Downstream Change Summary

Introduced new aggregation expressions for datetime duration operations - $dateAdd, $dateSubtract, $dateDiff.

Description of Linked Ticket

Update March 8, 2018

As of 3.7.3 via SERVER-30523 $dateFromParts allows constructing a date by adding or subtracting to/from existing month or day values, including handling overflow/underflow to get a valid date that's N months ago or from now, or N days ago or from now (same for years, hours, minutes, etc).

This ticket will track adding syntactic sugar to allow more straight forward syntax for such date operations as adding or subtracting days, months, hours, etc from another date.

There is currently no approved syntax, see below for proposed syntax.

Update May 1, 2014

Since our Date type assumes that days are fixed at 86400 seconds, it is already possible to use $add and $subtract time units up to a week since they are all a fixed number of milliseconds. Year/Quarter/Month are more complicated because they are not fixed units of time. Additionally they probably shouldn't use $add or $subtract syntax since they do not follow the usual mathematical laws. For example, 2012/02/29 + 1 year is either 2013/02/28 or 2013/03/01. Either way, subtracting 1 year from that does not get you back to 2012/02/29. These are the sorts of questions we will need good answers to before we can tackle this ticket.

Original Description

It would be nice to count with something like DATE_ADD and DATE_SUB of MySQL where you can add or subtract intervals, not only milliseconds like the $add expression is doing now.
Right now all the add/subtract operations with date are handled like numbers, and some cases this is not enough, like adding months or years.

I propose to create new expressions to handle date addition/subtraction supporting units and also milliseconds. Like:

db.test.aggregate( { $project: { expiryDate: { $dateAdd: [ "$date", [5, "months"] ] } } });
db.test.aggregate( { $project: { lastYear: { $dateSub: [ "$date", [1, "years"] ] } } });

Scope of changes

Impact to Other Docs

MVP (Work and Date)

Resources (Scope or Design Docs, Invision, etc.)


Generated at Thu Feb 08 08:09:37 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.