[SERVER-23347] Date difference operator Created: 25/Mar/16  Updated: 06/Dec/22

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

Type: Improvement Priority: Minor - P4
Reporter: James Moger Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-10651 Aggregation expressions to add/subtra... Closed
Assigned Teams:
Query Optimization
Participants:

 Description   

It would be really nice to have a flexible date difference operator that calculates the difference between two dates resulting in a duration (e.g. duration in msecs, secs, mins, hours, etc).

Consider the following projection stage in an aggregation pipeline. I need to calculate the difference in days between two dates - and one date may be null.

  
{ $project: {
  DIM: {
      $floor: {
        $divide: [
          {$subtract: [ {$ifNull: [ '$DDAT', new Date()]}, '$FDAT' ]},
          86400000
        ]
      }
    }
  }
}

This works - and it's fast - but it would be more pleasant if I could specify something like:

{ $project: {
  DIM: {
      $diff: [ {$ifNull: [ '$DDAT', new Date()]}, '$FDAT', 'd' ]
    }
  }
}

ArangoDB offers a date difference function which is pretty nice,

DATE_DIFF(date1, date2, unit, asFloat): Calculate the difference between two dates in given time unit, optionally with decimal places. Returns a negative value if date1 is greater than / after date2.

unit can be either of the following to specify the time unit to return the difference in (case- insensitive):

y, year, years
m, month, months
w, week, weeks
d, day, days
h, hour, hours
i, minute, minutes
s, second, seconds
f, millisecond, milliseconds



 Comments   
Comment by Asya Kamsky [ 08/Jan/20 ]

A user at a local event asked for this as they calculate age many times and it would be simple and very readable to see

{$dateDiff: ["$$NOW", "$dateOfBirth", "years"]}

than what people are currently using

// more correct but more complex:
{$subtract:[
     {$subtract:[{$year:"$$NOW"},{$year:"$dateOfBirth"}]},
     {$cond:[
          {$gt:[0, {$subtract:[{$dayOfYear:"$$NOW"},
          {$dayOfYear:"$dateOfBirth"}]}]},
          1,
          0
     ]}
]}
 
// or far less correct but much more common for people to use
{$divide:[
     {$subtract:["$$NOW", "$dateOfBirth"]},
     1000*60*60*24*365
]}

Comment by Ramon Fernandez Marina [ 18/May/16 ]

Thanks for the clarification jamesmoger; while I think it makes sense for this request to be addressed at the same time as SERVER-10651, I agree the functionality is different so I've reopened this ticket and sent it downstream for consideration.

Regards,
Ramón.

Comment by James Moger [ 16/May/16 ]

My request is similar, but not the same.

  1. SERVER-10651 is requesting a function to add/subtract a duration to/from a date resulting in a new date.
  2. My request is to calculate the difference between two dates resulting in a duration (e.g. duration in msecs, secs, mins, hours, etc)
Comment by Ian Whalen (Inactive) [ 06/May/16 ]

Hi James, we believe this is a dupe of SERVER-10651 and are closing it as such. If you think we've misread this, please do reopen and let us know why.

Comment by Ramon Fernandez Marina [ 25/Mar/16 ]

Thanks for your suggestion jamesmoger, it has been sent to the Query team for evaluation. Please continue to watch this ticket for updates.

Regards,
Ramón.

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