[SERVER-10651] Aggregation expressions to add/subtract Years/Quarters/Months Created: 30/Aug/13 Updated: 08/Feb/23 Resolved: 21/Jan/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 2.5.2 |
| Fix Version/s: | 4.9.0 |
| Type: | New Feature | Priority: | Minor - P4 |
| Reporter: | David Ribba | Assignee: | Backlog - Query Execution |
| Resolution: | Done | Votes: | 5 |
| Labels: | aggregation, expression, pm1457-nominee, pull-request | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||
| Assigned Teams: |
Query Execution
|
||||||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||
| Description |
Update March 8, 2018As of 3.7.3 via 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, 2014Since 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 DescriptionIt 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. I propose to create new expressions to handle date addition/subtraction supporting units and also milliseconds. Like:
|
| Comments |
| Comment by Mindaugas Malinauskas [ 21/Jan/21 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Introduced new aggregation expressions for datetime duration operations in 5.0 - $dateAdd, $dateSubtract, $dateDiff. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 28/Sep/20 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
> Right now the workaround is to pull all the objects down in code and do the computation for month math in code.
That is not accurate - since 4.0 you've been able to use $dateToParts and $dateFromParts for this in the server. Example:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dan Cumings [ 23/Sep/20 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Adding hours and days is easy. Adding/subtracting months is not possible. Right now the workaround is to pull all the objects down in code and do the computation for month math in code. It blows me away that we still don't have a solution for this. This seems like it should have been one of the earlier things that should have been addressed with mongo. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 30/Jun/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
While I agree that ability to add interval should exist, I concur with comment from david-unergie that if the expressions to add date do not handle timezone then they will be simply syntactic sugar for doing date computation. For instance to just add 24 hours to a date we already can use
date2 is adding 24 hours (one day in a naive way, date3 is adding one day in Paris tz (which happens to have been 25 hours due to time change). | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by David Brochoire [ 04/Jun/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi! Looking at this issue, my 2 cents about the syntax: an object could be dangerous. For example, the object { year: 1, day: 5 } looks simple, but could be ambiguous. If I compare with [moment|https://www.npmjs.com/package/moment]:
Apart from that, I'm completely agree to have more syntactic sugar When working with timezones, adding 1 day is quite complicated with the current syntax, because 1 day is not always 24 hours. One example with the timezone Europe/Paris during the summer/winter time changeover:
At the present time, we have to do:
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 28/May/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
dcumin39 Did you mean to suggest an array - an object
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Dan Cumings [ 26/Apr/19 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
A combination of the original post and Asya Kamsky's idea. How about something like this
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 26/Mar/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
There is this syntax option:
Default for all not-provided periods would be 0. That example says add 5 years and 10 days to oldDate - similar to Oracle, but more like our $dateFromParts - $dateFromAnotherDatePlusParts.... | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 08/Mar/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Note that as of development release 3.7.3 it is possible to do interval math via $dateFromParts as Charlie described.
Similarly for adding or subtracting days - in the next release it will correctly do the date arithmetic. I will retitle this ticket to make it clear that it's tracking adding syntactic sugar for such expressions. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 23/Jan/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
arg20 note that When that's solved though, it's still a workaround so this ticket would continue tracking more direct syntax of specifying addition/subtraction of intervals to/from dates. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gabriel Zimmermann [ 05/Dec/17 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Hi @Charlie Swanson, I was actually implementing that today but it doesn't actually work because incrementing the day at the last day of the month should put the day number back to 1 and actually increment the month by 1. So when I'm running your code, if it is the `31st`, it actually gives me an error when I use `$dateFromParts` because day's value is `32` and so it throws this error: `'day' must evaluate to an integer in the range 1 to 31, found 32` | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Charlie Swanson [ 14/Nov/17 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
arg20 we haven't made specific headway on this particular request of directly adding dates with times, but coming in 3.6 are a couple new date expressions which I think make this possible:
Hope that helps! | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gabriel Zimmermann [ 14/Nov/17 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
No updates on this? Date operations are important for reporting specially in IoT applications. I need this feature to be able to calculate in mongo certain boundaries for my reports based on the data we have. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by David Ribba [ 30/Aug/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Created a pull request. |