how to write query in mongo db based on string date

Question

Order Collection

[
  {
    id: 1,
    OrderStatus: "Shipped",
    "PurchaseDate" : "2018-01-29T20:59:55Z",
  },
  {
    id: 2,
    OrderStatus: "Shipped",
    "PurchaseDate" : "2018-01-30T20:59:55Z",
  },
  {
    id: 3,
    OrderStatus: "Shipped",
    "PurchaseDate" : "2018-01-30T20:59:55Z",
  }
];

Order Detail Collection

[
  {
    orderid: 1,
    "QuantityShipped" : "1",
    "Amount" : "10",
  },
  {
    orderid: 2,
    "QuantityShipped" : "2",
    "Amount" : "20",
  },
  {
    orderid: 3,
    "QuantityShipped" : "1",
    "Amount" : "30",
  },
{
    orderid: 3,
    "QuantityShipped" : "1",
    "Amount" : "30",
  }

];

Result:

{
  sales: 80,
  count: 3
}

This is result will get when based on purchase date 2018-01-30T20:59:55Z. First we need to subtract 7 hours from purchase date then do filter. lets say if i subtract 7 hours 2018-01-30T20:59:55Z than may or may be not date will change. then we have to filter based on date.

Sales will calculate based on sum of order detail collection. Count will calculated based on orderdetail count.

how to write query in mongo db.

When you filter on date after subtraction, what does that filter look like... is it exact match on date or range and is the date filter coming as string or date and what format. Is it something like this: $match:{PurchaseDate: filterDate} — ambianBeing
– ambianBeing, Commented Oct 16, 2019 at 13:08

Devratna · Accepted Answer · 2019-10-16 11:35:12Z

1

You can use $dateFromString it converts a date/time string to a date object, and then you can simply put groupby query and get this result

follow this link https://docs.mongodb.com/manual/reference/operator/aggregation/dateFromString/

answered Oct 16, 2019 at 11:35

Devratna

1,0081 gold badge8 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

abhishekgautam76 Over a year ago

Can you write query. or how can we subtract 7 hours from date then where condtion should be match.

Collectives™ on Stack Overflow

how to write query in mongo db based on string date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related