How to write a Spring Data MongoDB query using Date manipulation

Question

I want to write a mongodb query using spring data repository @Query annotation and here is my requirement:

Get all the Vins that were added in the last sixty minutes and they are still active

"keepAlive" is the time in milliseconds a particular VIN is active. When a record is entered in database, "keepAlive" can be set as 30 minutes, 60 minutes etc

Example data:

{ vin: "ANBCDERGGGHHGUTY", keepAlive: "3600000", dateAdded: "2019-12-16T16:45:29-05:00" }
{ vin: "T5765ERGGGHHGUTX", keepAlive: "1800000", dateAdded: "2019-11-14T13:41:29-03:00" }

Here are my classes:

public class MyEntity {
    private String vin;
    private long keepAlive;
    private Date dateAdded;
}

I have tried something like this but it doesn't seem to work:

@Query(value = "{'keepAlive':{$lte : {$subtract: [?0, 'dateAdded']}}}")
List<MyEntity> findLatestVins(Date currentSystemDate);

Any idea how can I do this?

a side note: 60000 ms is not 60 minutes, it's 1 minute.

sudipn
– sudipn

2019-12-17 13:26:10 +00:00
Commented Dec 17, 2019 at 13:26 — sudipn
– sudipn, Commented Dec 17, 2019 at 13:26
Thank you, updated the times.

Arsalan Siddiqui
– Arsalan Siddiqui

2019-12-17 14:21:03 +00:00
Commented Dec 17, 2019 at 14:21 — Arsalan Siddiqui
– Arsalan Siddiqui, Commented Dec 17, 2019 at 14:21

sudipn · Accepted Answer · 2019-12-20 07:52:09Z

1

the sample data db.cars.find():

{ "vin" : 1, "keepAlive" : 3600000, "dateAdded" : "2019-12-17T15:00:29+01:00" }
{ "vin" : 2, "keepAlive" : 3600000, "dateAdded" : "2019-12-17T13:00:29+01:00" }
{ "vin" : 3, "keepAlive" : 1800000, "dateAdded" : "2019-12-17T15:00:29+01:00" }

please note that the field keepAlive is of type number

on the mongodb console:

db.cars.find({
    $expr: {
        $gte: [
            "$keepAlive", 
            { 
                $subtract: [
                    new Date(), 
                    {$toDate: "$dateAdded"}
                ]
            }
        ]
    }
})

version 3.2 compatible (using aggregation pipeline):

db.collection.aggregate( [ { }, ... ] )

> db.cars.aggregate([
    {
        $project: {
            vin: 1, 
            keepAlive:1, 
            dateAdded:1, 
            notExpired: {
                $gte:[
                    "$keepAlive",
                    { 
                        $subtract:[
                            new Date(),
                            { $toDate:"$dateAdded" }
                        ]
                    }
                ]
            }
        }
    },
    {
        $match: { notExpired: true }
    },
    {
        $project: { notExpired: 0 }
    }
])

edited Dec 20, 2019 at 7:52

answered Dec 17, 2019 at 14:31

sudipn

6091 gold badge5 silver badges14 bronze badges

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

4 Comments

Arsalan Siddiqui Over a year ago

Hey man, that's excellent, it works like a charm. Some questions: when do we use $expr?

sudipn Over a year ago

while solving, I started along the same line as you did, using the $gte inside the find. But the syntax of $gte is {field: {$gte: value} }, that means we have to provide a value for comparison and not an expression. Therefore the subtract expression did not work for you. Inside an $expr block, arguments can be any valid aggregation expression and so is $subtract.

Arsalan Siddiqui Over a year ago

Hey Sudipn, it turns out our mongo version 3.2 doesn't support "$expr", is there a mongo version 3 compatible query we can use.

sudipn Over a year ago

added a 3.2v compatible answer. have a look

Collectives™ on Stack Overflow

How to write a Spring Data MongoDB query using Date manipulation

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related