CODEWITHSUNDEEP
mongodbaggregation-framework
user1439609
user1439609

Reputation: 79

MongoDB get data at specific intervals in time

I have my data in MongoDb in the following format:

/* 1 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbaf"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:11.000Z"),
    "WIZ_VAR_10" : 710.0,
    "WIZ_VAR_11" : 910.0,
    "WIZ_VAR_12" : 910.0,
    "TIMESTAMP_EP" : 1483854251.0
}

/* 2 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb0"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:12.000Z"),
    "WIZ_VAR_10" : 720.0,
    "WIZ_VAR_11" : 920.0,
    "WIZ_VAR_12" : 920.0,
    "TIMESTAMP_EP" : 1483854252.0
}

/* 3 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb1"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:13.000Z"),
    "WIZ_VAR_10" : 730.0,
    "WIZ_VAR_11" : 930.0,
    "WIZ_VAR_12" : 930.0,
    "TIMESTAMP_EP" : 1483854253.0
}

/* 4 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb2"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:14.000Z"),
    "WIZ_VAR_10" : 740.0,
    "WIZ_VAR_11" : 940.0,
    "WIZ_VAR_12" : 940.0,
    "TIMESTAMP_EP" : 1483854254.0
}

/* 5 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb3"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:15.000Z"),
    "WIZ_VAR_10" : 750.0,
    "WIZ_VAR_11" : 950.0,
    "WIZ_VAR_12" : 950.0,
    "TIMESTAMP_EP" : 1483854255.0
}

/* 6 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb4"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:16.000Z"),
    "WIZ_VAR_10" : 760.0,
    "WIZ_VAR_11" : 960.0,
    "WIZ_VAR_12" : 960.0,
    "TIMESTAMP_EP" : 1483854256.0
}

/* 7 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb5"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:17.000Z"),
    "WIZ_VAR_10" : 770.0,
    "WIZ_VAR_11" : 970.0,
    "WIZ_VAR_12" : 970.0,
    "TIMESTAMP_EP" : 1483854257.0
}

/* 8 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb6"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:18.000Z"),
    "WIZ_VAR_10" : 780.0,
    "WIZ_VAR_11" : 980.0,
    "WIZ_VAR_12" : 980.0,
    "TIMESTAMP_EP" : 1483854258.0
}

/* 9 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb7"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:19.000Z"),
    "WIZ_VAR_10" : 790.0,
    "WIZ_VAR_11" : 990.0,
    "WIZ_VAR_12" : 990.0,
    "TIMESTAMP_EP" : 1483854259.0
}

/* 10 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb8"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:20.000Z"),
    "WIZ_VAR_10" : 800.0,
    "WIZ_VAR_11" : 0.0,
    "WIZ_VAR_12" : 0.0,
    "TIMESTAMP_EP" : 1483854260.0
}

/* 11 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb9"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:21.000Z"),
    "WIZ_VAR_10" : 810.0,
    "WIZ_VAR_11" : 10.0,
    "WIZ_VAR_12" : 10.0,
    "TIMESTAMP_EP" : 1483854261.0
}

/* 12 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbba"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:22.000Z"),
    "WIZ_VAR_10" : 820.0,
    "WIZ_VAR_11" : 20.0,
    "WIZ_VAR_12" : 20.0,
    "TIMESTAMP_EP" : 1483854262.0
}

What I would like to achieve is get the records at 5 sec intervals. So the output I expect should be 

/* 1 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbaf"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:11.000Z"),
    "WIZ_VAR_10" : 710.0,
    "WIZ_VAR_11" : 910.0,
    "WIZ_VAR_12" : 910.0,
    "TIMESTAMP_EP" : 1483854251.0
}
/* 6 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb4"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:16.000Z"),
    "WIZ_VAR_10" : 760.0,
    "WIZ_VAR_11" : 960.0,
    "WIZ_VAR_12" : 960.0,
    "TIMESTAMP_EP" : 1483854256.0
}
/* 11 */
{
    "_id" : ObjectId("5a40f49743bf8220c865cbb9"),
    "TIMESTAMP" : ISODate("2017-01-08T11:14:21.000Z"),
    "WIZ_VAR_10" : 810.0,
    "WIZ_VAR_11" : 10.0,
    "WIZ_VAR_12" : 10.0,
    "TIMESTAMP_EP" : 1483854261.0
}

So essentially I want to get the records at an interval. I checked the aggregation framework but could not find exact solution for the same. Any help is highly appreciated.

Upvotes: 1

Views: 204

Answers (1)

mickl
mickl

Reputation: 49945

You can use $second operator which takes seconds part from date (0-60) and then $mod to take modulo 5 (5 seconds interval). In your case you want to start with 11 so you should check if modulo is $eq to 1. To use those operators directly in $match you need $expr which is available in MongoDB v 3.6:

db.col.aggregate([
    {
        $match: {
            $expr: {
                $eq: [ { $mod: [ { $second: "$TIMESTAMP" }, 5 ] }, 1 ]
            }            
        }
    }
])

If you're using lower MongoDB version then you can try with $redact:

db.col.aggregate([
    {
        $redact: {
            $cond: {
                if: { $eq: [ { $mod: [ { $second: "$TIMESTAMP" }, 5 ] }, 1 ] },
                then: "$$KEEP",
                else: "$$PRUNE"
            }            
        }
    }
])

Upvotes: 1

Related Questions