The $subtract operator for projections in an aggregation pipeline (cf. unit tests) does not support DateTime types properly.
For instance, I want to calculate the number of days elapsed between now and some date coming from a previous stage of my pipeline.
To do that I would:
- Subtract that date from the current date (which returns the delta in milliseconds, cf docs)
- Divide the result by the number of milliseconds in a day
- Floor the whole thing to get a nice int-ish number
Something like this:
{ "$project": { "nbDays": { "$floor": { "$divide": [ { "$subtract": [ "new ISODate()", "$dateFieldFromAbove" ] }, "1000 * 60 * 60 * 24" ] } } } }
I would expect to be able to write this like this in Linq (which compiles nicely and makes sense):
Project(x => new { nbDays = Math.Floor( (DateTime.UtcNow - x.dateFieldFromAbove).TotalMilliseconds / 1000 * 60 * 60 * 24 ) })
But in C# the difference between two DateTime objects produces a TimeSpan, from which we need to take the TotalMilliseconds for the division to work ....
This property is not supported by the translator.
Member TotalMilliseconds of type System.TimeSpan in the expression tree (22/11/2017 10:50:32 - {document}{dateFieldFromAbove}).TotalMilliseconds cannot be translated.
Is there anything I am missing here? Is there a workaround to deal with DateTime subtractions other than switching back to the painful BsonDocument pipeline? Any plan to support this out of the box in future version of the driver?