How to Use timeResolution Variable in MongoDB Query with Grafana Without Converting to Milliseconds

I'm working with MongoDB and Grafana, and I need to use a timeResolution variable (interval) with time values like 500ms, 1s, 15s, 30s, 1m, 5m, 30m, 1h, 12h in an aggregation query. Currently, I am converting the values to milliseconds in my query as follows:

db.test_1.aggregate([
  { $unwind: "$PayloadGEOJSON.features" },
  { $match: { "PayloadGEOJSON.features.properties.name": "G002" } },
  {
    $addFields: {
      timeResolutionMs: {
        $switch: {
          branches: [
            { case: { $eq: ["$timeResolution", "500ms"] }, then: 500 },
            { case: { $eq: ["$timeResolution", "1s"] }, then: 1000 },
            { case: { $eq: ["$timeResolution", "15s"] }, then: 15000 },
            { case: { $eq: ["$timeResolution", "30s"] }, then: 30000 },
            { case: { $eq: ["$timeResolution", "1m"] }, then: 60000 },
            { case: { $eq: ["$timeResolution", "5m"] }, then: 300000 },
            { case: { $eq: ["$timeResolution", "30m"] }, then: 1800000 },
            { case: { $eq: ["$timeResolution", "1h"] }, then: 3600000 },
            { case: { $eq: ["$timeResolution", "12h"] }, then: 43200000 }
          ],
          default: 0
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      time: {
        $toDate: {
          $multiply: [
            {
              $subtract: [
                { $toDouble: "$PayloadGEOJSON.features.properties.timeStamp" },
                {
                  $mod: [
                    { $toDouble: "$PayloadGEOJSON.features.properties.timeStamp" },
                    { $toInt: "$timeResolutionMs" }
                  ]
                }
              ]
            },
            1000
          ]
        }
      },
      movementType: "$PayloadGEOJSON.features.properties.movementType",
      stopType: "$PayloadGEOJSON.features.properties.stopType"
    }
  },
  { $sort: { time: 1 } }
]);

Is there a way to use the timeResolution variable directly without converting it to milliseconds in the MongoDB query? I'm looking for a more efficient approach or any other strategies I might employ.

Thanks for your help!

I have tried to read the timeResolution variable in various ways, but none of my attempts have successfully captured the variable. I've explored several approaches, but I haven't managed to get the query to recognize or use the timeResolution variable as intended.

Upvotes: 0

Views: 45

Answers (0)

Related Questions