0

maybe someone can help me with my problem: I have some sensors and if the value changes, it fires an event to write the data in the MongoDB (v4.4). What i've managed is to group all the data into 24 hours with an average value, but with the sort method i can only sort from hour 00h...23h or 23h...00h - but i need to start exactly at the current hour and sort the data 24h back from there.

Let's say we currently have 01:19 - it must be that way sorted (for a X-axis, from left to right): 02h-03h-04h-...23h-00h-01h, with the last hour (01h on the right) holding the last current value of that sensor in that array.

const result = db.collection('sensors').aggregate([
        {
            $match: {
                "path": path,
                "timestamp": { $gte: new Date((new Date().getTime() - (24 * 60 * 60 * 1000))) }
            }
        },
        {
            $group: {
                _id: {
                    $hour: "$_id"
                },
                avg_value: {
                    $avg: { "$toDouble": "$value"}
                },
            }
        },
        {
            $sort: {
                "_id": 1
            }
        },
    ]);

The _id is the MongoDB index object, value the sensor value and timestamp an additional new Date(Date.now()) at the sensor measurement.

UPDATE Here is the example data from MongoDB:

{
    "_id": {
        "$oid": "62606ffd5705ba9a5a79e0e1"
    },
    "path": "weather/outsideTemperature",
    "timestamp": {
        "$date": "2022-04-20T20:41:33.067Z"
    },
    "value": "1.1"
}
{
    "_id": {
        "$oid": "626076875705ba9a5a79e5fc"
    },
    "path": "weather/outsideTemperature",
    "timestamp": {
        "$date": "2022-04-20T21:09:27.328Z"
    },
    "value": "1.0"
}
{
    "_id": {
        "$oid": "626083395705ba9a5a79ef27"
    },
    "path": "weather/outsideTemperature",
    "timestamp": {
        "$date": "2022-04-20T22:03:37.652Z"
    },
    "value": "0.9"
}

Greetings, patsuhiko

patsuhiko
  • 1
  • 1
  • (Not familiar with MongoDB, just going through the approval queue) Is there any reason why you can't store the date as well? If you store that as ISO8601 timestamps, then you can just drop the minutes and seconds, and you have a sort (and filter) key which allows you to look back as many hours as you want. – Axman6 Apr 22 '22 at 00:08
  • It would be better if you can provide testing data and expected result in a valid JSON by editing your own question. Ask a question like [this](https://stackoverflow.com/questions/71835902/excract-only-the-lookups-return-mongoose) would be more clarify for us to solve your problem. Make sure that your testing data can come up with your expected result in certain logic. – YuTing Apr 22 '22 at 01:07
  • Thank you - updated 3 MongoDB data documents. – patsuhiko Apr 22 '22 at 07:50

0 Answers0