Validation Between Date In Javascript

Question

I have object

var items = [
[{
    id: 1
    start: "2015-03-01 21:50:00",
    end: "2015-03-01 21:51:00"
}],
[{
    id: 2
    start: "2015-03-01 21:50:06",
    end: "2015-03-01 21:52:00"
}],
[{
    id: 3
    start: "2015-03-01 21:50:08",
    end: "2015-03-01 21:51:40"
}],
[{
    id: 4
    start: "2015-03-01 21:50:16",
    end: "2015-03-01 21:52:50"
}],
[{
    id: 5
    start: "2015-03-01 21:50:30",
    end: "2015-03-01 21:54:50"
}],
[{
    id: 6
    start: "2015-03-02 21:50:59",
    end: "2015-03-01 21:54:08"
  }]
];

Same like I have lacks of rows, I have to calculate how many have records in 1 minutes with start time and end_time parameters.

How can I achieve this with javascript and mongodb.

possible duplicate of [Get difference between 2 dates in javascript?](http://stackoverflow.com/questions/3224834/get-difference-between-2-dates-in-javascript) — Volkan Ulukut, Apr 30 '15 at 13:04
Is that a collection? Your question is a bit unclear on the mongodb part, if you want to do this in mongodb, how is your document schema defined? — chridam, Apr 30 '15 at 13:33
yes its collection. if its possible to get result with mongo its good. — Andy, Apr 30 '15 at 13:43

score 0 · Answer 1 · answered Apr 30 '15 at 14:19

You would want to compare the two fields, get the difference in minutes for start and end fields. You could either do this in two ways.

The first approach would be to use the $where operator in your find() query. Suppose your collection items is made up of these documents:

db.items.insert([
{
    _id: 1,
    start: ISODate("2015-03-01 21:50:00"),
    end: ISODate("2015-03-01 21:51:00")
},
{
    _id: 2,
    start: ISODate("2015-03-01 21:50:06"),
    end: ISODate("2015-03-01 21:52:00")
},
{
    _id: 3,
    start: ISODate("2015-03-01 21:50:08"),
    end: ISODate("2015-03-01 21:51:40")
},
{
    _id: 4,
    start: ISODate("2015-03-01 21:50:16"),
    end: ISODate("2015-03-01 21:52:50")
},
{
    _id: 5,
    start: ISODate("2015-03-01 21:50:30"),
    end: ISODate("2015-03-01 21:54:50")
},
{
    _id: 6,
    start: ISODate("2015-03-02 21:50:59"),
    end: ISODate("2015-03-01 21:54:08")
 }
])

using the $where operator to get those documents with a one minute duration between start and end dates (assuming those times are on the same day), the query:

db.items.find({"$where": "this.end.getMinutes() - this.start.getMinutes() == 1"});

returns

/* 0 */
{
    "_id" : 1,
    "start" : ISODate("2015-03-01T21:50:00.000Z"),
    "end" : ISODate("2015-03-01T21:51:00.000Z")
}

/* 1 */
{
    "_id" : 3,
    "start" : ISODate("2015-03-01T21:50:08.000Z"),
    "end" : ISODate("2015-03-01T21:51:40.000Z")
}

However, there are some points worth considering when using the $where operator. From the docs:

$where evaluates JavaScript and cannot take advantage of indexes. Therefore, query performance improves when you express your query using the standard MongoDB operators (e.g., $gt, $in). In general, you should use $where only when you can’t express your query using another operator. If you must use $where, try to include at least one other standard query operator to filter the result set. Using $where alone requires a table scan.

The second approach would be to use MongoDB's aggregation framework. Your pipeline would typically be as follows:

db.items.aggregate([
    {
        "$group": {
            "_id": {"_id": "$_id",
            "start_year": { "$year": "$start" },
            "start_month": { "$month": "$start" },
            "start_day": { "$dayOfMonth": "$start" },
            "start_hour": { "$hour": "$start" },
            "start_minutes": { "$minute": "$start" },
            "end_year": { "$year": "$end" },
            "end_month": { "$month": "$end" },
            "end_day": { "$dayOfMonth": "$end" },
            "end_hour": { "$hour": "$end" },
            "end_minutes": { "$minute": "$end" }},
            "data": {
                "$addToSet": "$$ROOT"
            }
        }
    },
    {
        "$project": {
            "isMinuteDuration": {
                "$eq": [
                    { "$subtract": [ "$_id.end_minutes", "$_id.start_minutes" ] },
                    1                   
                ]
            },            
            "data": 1,
            "_id": 0
        }
    },
    {
        "$match": {
            "isMinuteDuration": true
        }
    },
    {
        "$unwind": "$data"
    },
    {
        "$project": {
            "_id": "$data._id",
            "start": "$data.start",
            "end": "$data.end"
        }
    }
])

Output

/* 0 */
{
    "result" : [ 
        {
            "_id" : 3,
            "start" : ISODate("2015-03-01T21:50:08.000Z"),
            "end" : ISODate("2015-03-01T21:51:40.000Z")
        }, 
        {
            "_id" : 1,
            "start" : ISODate("2015-03-01T21:50:00.000Z"),
            "end" : ISODate("2015-03-01T21:51:00.000Z")
        }
    ],
    "ok" : 1
}

Validation Between Date In Javascript

1 Answers1