[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:
Depends
Documented
is documented by DOCS-14145 Investigate changes in SERVER-10651: ... Closed
Related
related to SERVER-9626 Aggregation Framework needs to suppor... Closed
is related to SERVER-11118 Add dateToString aggregation operator Closed
is related to SERVER-23347 Date difference operator Backlog
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Participants:
Case:

 Description   
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"] ] } } });



 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: 
 

db.test.aggregate([
	{
		"$project" : {
			"_id" : ISODate("2018-10-27T22:00:00Z")
		}
	},
	{
		"$addFields" : {
			"date2" :  {$dateFromParts:{
                                year:{$year:"$_id"}, 
                                month:{$subtract:[ {$month:"$_id"}, 6]},
                                day:{$dayOfMonth:"$_id"}
                          }}, 
			"date3" :  {$dateFromParts:{
                                year:{$year:"$_id"}, 
                                month:{$add:[ {$month:"$_id"}, 6]},
                                day:{$dayOfMonth:"$_id"}
                          }}, 
 
                 }
         } 
])
{ "_id" : ISODate("2018-10-27T22:00:00Z"), "date2" : ISODate("2018-04-27T00:00:00Z"), "date3" :  ISODate("2019-04-27T00:00:00Z") }
 

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

 {$add:[ "$date1", 1000*60*60*24]}

. Using the example above with and without considering time zone (and simplifying to a single stage):

db.test.aggregate([
	{
		"$project" : {
			"_id" : ISODate("2018-10-27T22:00:00Z"),
			"tz" : "Europe/Paris"
		}
	},
	{
		"$addFields" : {
			"date2" : {
				"$add" : [
					"$_id",
					86400000
				]
			},
			"date3" : {
				"$let" : {
					"vars" : {
						"dt" : {
							"$dateToParts" : {
								"date" : "$_id",
								"timezone" : "$tz"
							}
						}
					},
					"in" : {
						"$dateFromParts" : {
							"year" : "$$dt.year",
							"month" : "$$dt.month",
							"day" : {
								"$add" : [
									1,
									"$$dt.day"
								]
							},
							"hour" : "$$dt.hour",
							"minute" : "$$dt.minute",
							"second" : "$$dt.second",
							"millisecond" : "$$dt.millisecond",
							"timezone" : "$tz"
						}
					}
				}
			}
		}
	}
])
{
	"_id" : ISODate("2018-10-27T22:00:00Z"),
	"date2" : ISODate("2018-10-28T22:00:00Z"),
	"date3" : ISODate("2018-10-28T23:00:00Z")
}

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]:

moment.utc('2016-02-26').add(1, 'year').add(5, 'day') => "2017-03-03"
moment.utc('2016-02-26').add(5, 'day').add(1, 'year') => "2017-03-02"

 

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: 

moment.tz('2018-10-28', 'Europe/Paris')
    => "2018-10-28T00:00:00.000+02:00"
 
moment.tz('2018-10-28', 'Europe/Paris').add(1, 'day')
    => "2018-10-29T00:00:00.000+01:00"

 

At the present time, we have to do: 

db.getCollection('test').aggregate([
    {
        $project: {
            _id: ISODate("2018-10-28T00:00:00.000+02:00"),
        }
    },
    {
        $project: {
            date1: {
                $dateToParts: {
                    date: '$_id',
                    timezone: 'Europe/Paris',
                }
            },
        },
    },
    {
        $project: {
            date1: true,
            date2: {
                $mergeObjects: [
                    '$date1',
                    {
                        day: {
                            $sum: [
                                '$date1.day',
                                1,
                            ]
                        }
                    }
                ],
            },
        },
    },
    {
        $project: {
            date1: true,
            date2: true,
            date3: {
                $dateFromParts: {
                    year: '$date2.year',
                    month: '$date2.month',
                    day: '$date2.day',
                    hour: '$date2.hour',
                    minute: '$date2.minute',
                    second: '$date2.second',
                    millisecond: '$date2.millisecond',
                    timezone: 'Europe/Paris',
                }
            },
        }
    },
])

Comment by Asya Kamsky [ 28/May/19 ]

dcumin39 Did you mean to suggest an array - an object

{"$date", {year:5, month:0, day:10}} 

is not a legal JSON object.

 [ "$date", {year:5, month:0, day:10} ] 

would be legal but I think it may be ambiguous...

Comment by Dan Cumings [ 26/Apr/19 ]

A combination of the original post and Asya Kamsky's idea. How about something like this

db.test.aggregate( { $project: { expiryDate: { $dateAdd: { "$date",{ year:5, month:0, day:10 }}} } }); 

Comment by Asya Kamsky [ 26/Mar/18 ]

There is this syntax option:

{$dateAdd:{
    input:"$oldDate",
    year:5,
    month:0,
    day:10
}}

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.

db.test.aggregate( { $project: { 
   expiryDate: { $dateFromParts: {
          year:{$year:"$date"}, 
          month:{$add:[5, {"$month":"$date"}]}, 
          day:{$dayOfMonth:"$date"}}
   }
}});

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 SERVER-30523 is tracking the work for handling "overflow" in date parts.

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:

> db.foo.find()
{ "_id" : ObjectId("5a02e3ea8b20a8644f79b1dd"), "x" : 1 }
> db.foo.aggregate([
  {$addFields: {dateParts: {$dateToParts: {date: "$_id", timezone: "America/New_York"}}}}
])
{ "_id" : ObjectId("5a02e3ea8b20a8644f79b1dd"), "x" : 1, "dateParts" : { "year" : 2017, "month" : 11, "day" : 8, "hour" : 6, "minute" : 0, "second" : 58, "millisecond" : 0 } }
 
> db.foo.aggregate([
  {$addFields: {dateParts: {$dateToParts: {date: "$_id", timezone: "America/New_York"}}}},
  {$addFields: {"dateParts.day": {$add: ["$dateParts.day", 1]}}}
])
{ "_id" : ObjectId("5a02e3ea8b20a8644f79b1dd"), "x" : 1, "dateParts" : { "year" : 2017, "month" : 11, "day" : 9, "hour" : 6, "minute" : 0, "second" : 58, "millisecond" : 0 } }
 
> db.foo.aggregate([
  {$addFields: {dateParts: {$dateToParts: {date: "$_id", timezone: "America/New_York"}}}},
  {$addFields: {"dateParts.day": {$add: ["$dateParts.day", 1]}}},
  // Reconstructing the date is a little bit annoying, see SERVER-30547 for making $dateFromParts accept simply "$dateParts".
  {$addFields: {newDate: {$dateFromParts: {year: "$dateParts.year", month: "$dateParts.month", day: "$dateParts.day", hour: "$dateParts.hour", minute: "$dateParts.minute", second: "$dateParts.second", millisecond: "$dateParts.millisecond"}}}}
])
{ "_id" : ObjectId("5a02e3ea8b20a8644f79b1dd"), "x" : 1, "dateParts" : { "year" : 2017, "month" : 11, "day" : 9, "hour" : 6, "minute" : 0, "second" : 58, "millisecond" : 0 }, "newDate" : ISODate("2017-11-09T06:00:58Z") }

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.
https://github.com/mongodb/mongo/pull/486

Generated at Thu Feb 08 03:23:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.