How do i search and query based on time?

Working with Data
,
20

@Bradley_Benjamin, here is the aggregation query which can return a result like follows for a trading pair (assuming relevant data exists):

{
        "latest_time_stamp" : ISODate("2022-02-03T14:45:00Z"),
        "latest_price" : 999,
        "trading_pair" : "2INCH-BTC",
        "prev_time_stamp" : ISODate("2022-02-03T14:40:00Z"),
        "prev_price" : 678,
        "percent_change" : 47.34513274336283
}

The Query:

db.collection.aggregate([
  { 
    $sort: { trading_pair: 1, time_stamp: -1 } 
  },
  { 
    $group: { 
        _id:  "$trading_pair",
        docs: { $push: "$$ROOT" }, 
        latest_time_stamp: { $first: "$time_stamp" }, 
        latest_price: { $first: "$price" }
    }
  },
  { 
    $addFields: {
        prev_doc: {
           $arrayElemAt: [
               { $filter: {
                      input: "$docs", 
                      as: "doc",
                      cond: { $eq: [ "$$doc.time_stamp", { $subtract: [ "$latest_time_stamp", 5 * 60 * 1000 ] } ] }
                }}, 0
           ]
        }
    }
  },
  {
     $project: {
         trading_pair: "$_id",
         _id: 0,
         latest_time_stamp: 1,
         latest_price: 1,
         prev_time_stamp: "$prev_doc.time_stamp",
         prev_price: "$prev_doc.price",
         percent_change: {
             $divide: [ { $multiply: [ { $subtract: [ "$latest_price", "$prev_doc.price" ] }, 100 ] }, "$prev_doc.price" ]
         }
    }
  },
])
2 Likes