[SERVER-23656] Current timestamp variable for aggregate expressions Created: 12/Apr/16  Updated: 27/Feb/21  Resolved: 02/Apr/19

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 4.1.10

Type: New Feature Priority: Major - P3
Reporter: Brendan Turner Assignee: Martin Neupauer
Resolution: Done Votes: 15
Labels: expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
Duplicate
is duplicated by SERVER-37713 Views - Date objects are evaluated at... Closed
is duplicated by SERVER-31317 createView is interpreting Date objec... Closed
is duplicated by SERVER-29193 Current date/time available in Aggreg... Closed
is duplicated by SERVER-28144 Allow $currentDate in queries Closed
Related
related to COMPASS-3490 "new Date()" support for aggregation ... Closed
is related to SERVER-22963 New value type that the server transl... Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 2019-04-08
Participants:
Case:

 Description   

I would like to store aggregate pipelines in a collection, which will make them easier to re-use in my company's multiple applications. We will need to add new aggregates often and this seems like the easiest way to allow each application to programmatically accommodate new aggregates inserted from a single administrative panel.

The main problem I've encountered is specifying the current date within the aggregate itself without injecting it at runtime, which is a little clumsy. '$currentDate' exists to update a document, but there is nothing similar for aggregates.

I'm proposing a '$$NOW' variable that can be used in aggregation expressions to represent the current datetime when evaluated.



 Comments   
Comment by Philip Sultanescu [ 02/May/20 ]

This works in the $addFields stage.

collection.aggregate([{$addFields: {timeField: "$$NOW"}}])

But doesn't work in $match stages or queries, why?

collection.aggregate([{$match: {timeField: {"$lte": "$$NOW"}}}])

Comment by Martin Neupauer [ 02/Apr/19 ]

The $$NOW expression has been implemented and will be available in the next version. Before that it can be tried out in the development version 4.1.10 and up.

The usage is fairly straightforward:

collection.aggregate([{$addFields: {timeField: "$$NOW"}}])

This expression can be used to retrieve a server-side wall clock time (the Date BSON type) with millisecond precision. Multiple references to "$$NOW" within the same command will all return the same date value.

More detailed documentation in the reference pages is forthcoming.

Comment by Robert Christ [ 23/Oct/18 ]

The proposed $$NOW suggestion would also close out the bug I just filed: SERVER-37713

Comment by Asya Kamsky [ 02/Apr/18 ]

I realized the question about when $$NOW is resolved has two components.

1) in aggregation stage with a single reference to "$$NOW" whether it should resolve once for all documents processed (as opposed to for each document, the way $currentDate works in update)
2) in aggregation statement where several pipeline stages reference "$$NOW" should the earlier "$$NOW" be the same value as the later stage "$$NOW"

Comment by Asya Kamsky [ 24/Mar/18 ]

> mongos has no idea about any of the views

I think that reinforces my feeling that "$$NOW" should resolve on each mongod as it executes, the same way "$currentDate" works and other commands that include local time.

Comment by Kyle Suarez [ 19/Mar/18 ]

asya:

I would think so - doesn't mongos resolve the view definition (which is where this would potentially be stored?)

The view definitions are stored on the primary shard of the cluster. mongos has no idea about any of the views; the primary shard simply tells it to rewrite the command on the view to be a different aggregation command on the underlying namespace. Not sure if this influences your opinion about where the time should be evaluated, though.

Comment by Asya Kamsky [ 19/Mar/18 ]

I just ran some tests and I realized that $currentDate will use a different time for each document in a multi-update, even on a single server. And I think that's correct - if it takes five minutes to update every document, current time is going to be different at the time of the first update and last update.

So I think maybe it should be like $currentDate and not like client-passed timestamp would be. That means if you have multiple stages using "$$NOW" then they will generate potentially slightly different timestamps.

I just did a quick test (just added a variable which gets evaluated every time it's encountered and not optimized into a constant and I see something that doesn't seem wrong:

db.conv.aggregate({$project:{n1:"$$NOW"}}, {$addFields:{a:{$range:[0,10000]}}},{$unwind:"$a"},{$addFields:{n2:"$$NOW"}})
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 0, "n2" : ISODate("2018-03-19T05:26:40.588Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 1, "n2" : ISODate("2018-03-19T05:26:40.588Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 2, "n2" : ISODate("2018-03-19T05:26:40.588Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 3, "n2" : ISODate("2018-03-19T05:26:40.588Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 4, "n2" : ISODate("2018-03-19T05:26:40.588Z") }
...
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 99, "n2" : ISODate("2018-03-19T05:26:40.588Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 100, "n2" : ISODate("2018-03-19T05:26:40.588Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 101, "n2" : ISODate("2018-03-19T05:26:40.619Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 102, "n2" : ISODate("2018-03-19T05:26:40.619Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 103, "n2" : ISODate("2018-03-19T05:26:40.619Z") }
...
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 906, "n2" : ISODate("2018-03-19T05:26:40.620Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 907, "n2" : ISODate("2018-03-19T05:26:40.621Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 908, "n2" : ISODate("2018-03-19T05:26:40.621Z") }
{ "_id" : ObjectId("5aaf172eb6ff2453053cd3ad"), "n1" : ISODate("2018-03-19T05:26:40.588Z"), "a" : 909, "n2" : ISODate("2018-03-19T05:26:40.621Z") }
...
{ "_id" : ObjectId("5aaf1731b6ff2453053cd3ae"), "n1" : ISODate("2018-03-19T05:26:40.632Z"), "a" : 6955, "n2" : ISODate("2018-03-19T05:26:40.643Z") }
{ "_id" : ObjectId("5aaf1731b6ff2453053cd3ae"), "n1" : ISODate("2018-03-19T05:26:40.632Z"), "a" : 6956, "n2" : ISODate("2018-03-19T05:26:40.643Z") }
{ "_id" : ObjectId("5aaf1731b6ff2453053cd3ae"), "n1" : ISODate("2018-03-19T05:26:40.632Z"), "a" : 6957, "n2" : ISODate("2018-03-19T05:26:40.643Z") }
...
{ "_id" : ObjectId("5aaf1731b6ff2453053cd3ae"), "n1" : ISODate("2018-03-19T05:26:40.632Z"), "a" : 9998, "n2" : ISODate("2018-03-19T05:26:40.647Z") }
{ "_id" : ObjectId("5aaf1731b6ff2453053cd3ae"), "n1" : ISODate("2018-03-19T05:26:40.632Z"), "a" : 9999, "n2" : ISODate("2018-03-19T05:26:40.647Z") }
{ "_id" : ObjectId("5aaf1733b6ff2453053cd3af"), "n1" : ISODate("2018-03-19T05:26:40.647Z"), "a" : 0, "n2" : ISODate("2018-03-19T05:26:40.647Z") }
{ "_id" : ObjectId("5aaf1733b6ff2453053cd3af"), "n1" : ISODate("2018-03-19T05:26:40.647Z"), "a" : 1, "n2" : ISODate("2018-03-19T05:26:40.647Z") }
...
{ "_id" : ObjectId("5aaf1816b6ff2453053cd3b0"), "n1" : ISODate("2018-03-19T05:26:40.659Z"), "a" : 9998, "n2" : ISODate("2018-03-19T05:26:40.671Z") }
{ "_id" : ObjectId("5aaf1816b6ff2453053cd3b0"), "n1" : ISODate("2018-03-19T05:26:40.659Z"), "a" : 9999, "n2" : ISODate("2018-03-19T05:26:40.671Z") }

Sounds like we need to reach a consensus on exactly when "$$NOW" gets evaluated (and where).

Note that the most frequent use case mentioned won't care about it since it will likely only be using the year-month-day of the current time to calculate "most recent three months" or that type of reporting interval.

Comment by Asya Kamsky [ 18/Mar/18 ]

in sharding, would mongos choose the value and send it to shards?

I would think so - doesn't mongos resolve the view definition (which is where this would potentially be stored?) Also mongos resolving it is most-like client passing its own time to all shards/servers.

In a multi-update, would each update see the same value or different values

We already allow setting current time via the unfortunate $currentDate modifier - I believe that gets evaluated on the server applying the update, no? Are you asking about some future hypothetical world in which updates can use aggregation expressions (PM-1021) and we can get rid of $currentDate and use this new expression in its place? If so, it actually makes more sense to me that the server applying the update make the evaluation, thinking about something like extremely long running update applying to 1,000 documents I would expect to see a later timestamp on the last updated document compared to the first one.

Does that contradict your preference? Is "each statement" a single multi-update statement? Or actual running operation?

Comment by Asya Kamsky [ 30/Jan/18 ]

This would also benefit any view definition that wants to filter on time range that's relative to "$$NOW".

Comment by Asya Kamsky [ 07/Dec/16 ]

$$NOW type system variable would be very useful for any sort of writes that aggregation pipeline does, similar to $currentDate on update, but without its restrictions.

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