Sort by date in Datetime field in mongo

Question

I am using django with mongo and I have this type of model:

class ProductDate(EmbeddedDocument):
    created = DateTimeField()
    updated = DateTimeField(null=True)

class Product(Document):
    product_id = IntField()
    saves = IntField(default=0)

    title = StringField(max_length=1000)
    gender = StringField(choices=settings.GENDER_CHOICES, default=settings.UNISEX, max_length=50)
    date = EmbeddedDocumentField(ProductDate)

Now with this when I make a query like this:

queryset = queryset.filter(title=search)

I want to order first by the date in the created DateTime field and then order by saves. That way products with same date, not time, are ordered by number of saves. However, I can't seem to find a way to convert the diatomite to date, so that it compares only the date.

I can't do:

.order_by("-date__created","-saves")

because that orders based on time.

I tried using aggregate in mongo,with this tutorial:

http://www.codewrecks.com/blog/index.php/2014/10/13/aggregate-in-mongo-using-only-date-part-of-a-datetime-property/

but that did't help me.

Could someone please guide me to how I can do this? Thank You!

Answer 1

You need to use the _get_collection() accesssor to get at the underlying .aggregate() method from a pymongo collection, and then use $project in order to create the truncated date information.

You can either use date aggregation operators:

Product._get_collection.aggregate([
    { "$project": {
        "product_id": 1,
        "saves": 1,
        "title": 1,
        "gender": 1,
        "date": 1,
        "partDate": {
            "year": { "$year": "$date.created" },
            "dayOfYear": { "$dayOfYear": "$date.created" }
        }
    }},
    { "$sort": { 
        "partDate.year": -1,
        "partDate.dayOfYear": -1, 
        "saves": -1
    }}
])

Or with date math instead:

Product._get_collection.aggregate([
    { "$project": {
        "product_id": 1,
        "saves": 1,
        "title": 1,
        "gender": 1,
        "date": 1,
        "truncDate": {
            "$subtract": [
                { "$subtract": [ 
                    "$date.created",
                    datetime.datetime.utcfromtimestamp(0)
                ] },
                { "$mod": [
                    { "$subtract": [ 
                        "$date.created",
                        datetime.datetime.utcfromtimestamp(0)
                    ] },
                    1000 * 60 * 60 * 24 
                ]}
            ]
        }
    }},
    { "$sort": { "truncDate": -1, "saves": -1 }}
])

The latter works by when you do math operations on a Date BSON object the result is a unix timestamp value from seconds from epoch. So subtracting the epoch date yields a timestamp value, that is then rounded to a single day.

-- EDIT --

Better yet, a little extra math and you return a BSON Date that translates back for API. Just apply $add to turn a numeric value back into a BSON Date:

Product._get_collection.aggregate([
    { "$project": {
        "product_id": 1,
        "saves": 1,
        "title": 1,
        "gender": 1,
        "date": 1,
        "truncDate": {
            "$add": [
                { "$subtract": [
                    { "$subtract": [ 
                        "$date.created",
                        datetime.datetime.utcfromtimestamp(0)
                    ]},
                    { "$mod": [
                        { "$subtract": [ 
                            "$date.created",
                            datetime.datetime.utcfromtimestamp(0)
                        ]},
                        1000 * 60 * 60 * 24 
                    ]}
                ]},
                datetime.datetime.utcfromtimestamp(0)
            ]
        }
    }},
    { "$sort": { "truncDate": -1, "saves": -1 }}
])

Which is the same basic epoch date application as used in the rest of the conversion.

-- END --

Then you can $sort on the appropriate field.

Of course these are "raw" objects and no longer mongoengine documents, but then aggregation output is rarely exactly the same as collection input. You can of course in this case, pick the relevant fields and re-cast into your document type if needed. Or otherwise just work with the results as is.