Fetch data with max and between condition

Working with Data
1

I am having data for one month, I need to fetch maximum data for a specific date.

ex: I need to fetch the maximum value for o5-Jan-2020 00:00:00 between 05-Jan-20200 23:59:59.
How to achieve this condition

3

:wave:

Hi @Aruljothy_Sundaramoo and welcome to the MongoDB community forums.

To do that type of mach, you would use the following:

db.collection.find({"date": {"$gte": ISODate("2020-01-05T00:00:00"), "$lte": ISODate("2020-01-05T23:59:59")}}).sort({dataField: -1}).limit(1)

The above will search for all documents that fall on the given date. It will then sort the values in the field dataField in a descending order and return just the top item. This could be done in an aggregation as well.

NOTE: Depending on how your data was entered and stored, you might need to adjust values for timezone differences from UTC.

1 Like
4

Nice! Thanks Doug! I was about to recommend similar!

1 Like
5

Thanks mate…but i need to get the max(highest) value, no the last value

6

The query will do that. By sorting in a descending fashion you you will get the maximum value of the field.

7

bu it tooks the last value not the highest value in the collection of specific date

8

Are you sorting on the field that has the values that you are looking for? Can you share your query and some sample data showing that the wrong document is being returned?

9

Given the following simple set of data:

> db.forumTest.find()
{ "_id" : ObjectId("5eb9bf88278631f5c0263830"), "date" : ISODate("2020-05-01T00:00:00Z"), "value" : 5 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263831"), "date" : ISODate("2020-05-01T03:00:00Z"), "value" : 12 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263832"), "date" : ISODate("2020-05-01T06:00:00Z"), "value" : 2 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263833"), "date" : ISODate("2020-05-01T09:00:00Z"), "value" : 1 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263834"), "date" : ISODate("2020-05-01T12:00:00Z"), "value" : 19 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263835"), "date" : ISODate("2020-05-01T15:00:00Z"), "value" : 3 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263836"), "date" : ISODate("2020-05-01T18:00:00Z"), "value" : 17 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263837"), "date" : ISODate("2020-05-01T21:00:00Z"), "value" : 7 }
{ "_id" : ObjectId("5eb9bf88278631f5c0263838"), "date" : ISODate("2020-05-02T00:00:00Z"), "value" : 55 }

Note that there are eight documents for May 1st and one for May 2nd. The document on May 2nd has the highest value in the value field. The highest value for May 1st is the document from 12:00:00 and has a value of 19.

If I run the following query:

db.forumTest.find({"date": {"$gte": ISODate("2020-05-01T00:00:00"), "$lte": ISODate("2020-05-01T23:59:59")}}).sort({value: -1}).limit(1)

I get back the expected document:

{ "_id" : ObjectId("5eb9bf88278631f5c0263834"), "date" : ISODate("2020-05-01T12:00:00Z"), "value" : 19 }
2 Likes
10

Seems did some mistaked while converting the time from double. it makes some issues…the date was logged as Double in mongo…now using the above method i can get the value… thanks mate :slight_smile:

Query User

db.data.find({"timeStamp": {"$gte": 1589221800000, "$lte": 1589308199000}}).sort({value: -1}).limit(1)

OUTPUT

/* 1 */
{
    "_id" : ObjectId("5eb9b6424fc5c77aaa81w466"),
    "timeStamp" : 1589229122937.0,
    "company_id" : "10001",
    "plant_id" : "10001",
    "device_id" : "10001",
    "value" : {
        "MEASEAS1" : 0,
        "MEASEAS2" : 2.15729898582806e-41,
        "MEAS3" : -151732604633088.0
    }
}
1 Like