Time difference on dates in $lookup

Question

I am trying to calculate with some dates in my aggregation and $lookup. I was first thinking of using a function within the aggregation, but JavaScript is not allowed to be used.

{
  $lookup: {
    from: "sales",
    let: { user: "$_id" },
    pipeline: [
      $project: {
        time: //// CALCULATE TIME DIFFERENCE BETWEEN $createdAt and $startedAt,
      }
    ]
    as: "sales"
  }
}

Is there any way to return something similar to this in my $project?

const calculate = (created, started) => {
    const result = moment(created).diff(moment(started))
    return result
}

Seems that you cant call external functions inside aggregation pipeline https://stackoverflow.com/a/25866626/5373869 — Doruk Güneş, Jul 03 '19 at 12:10
@DorukGüneş Thanks for clearing that up! Do you know how I could do a calculation with moment in the BSON aggregation? — vemund, Jul 03 '19 at 12:32
I think they're very similar, you have just to use different pipeline operators from the other question. — oniramarf, Jul 03 '19 at 12:47

score 2 · Accepted Answer · answered Jul 03 '19 at 12:48

As I pointed out in the possible duplicate of another question, you should use one of the aggregation pipeline operators. In particular, you should use $subtract operator, that works also with dates.

{
  $lookup: {
    from: "sales",
    let: { user: "$_id" },
    pipeline: [
      $project: {
        time: { $subtract: ["$createdAt", "$startedAt"]}
      }
    ]
    as: "sales"
  }
}

Time difference on dates in $lookup

1 Answers1