sort mongodb documents by timestamp (in desc order)

Question

I have a bunch of documents in mongodb and all have a timestamp field with timestamp stored as "1404008160". I want to sort all docs in this collection by desc order. I do it by:

sort = [('timestamp', DESCENDING)]
collection.find(limit=10).sort(sort)

However, I don't get results sorted by timestamp in desc order. I am thinking it's because timestamp is being treated as an int field. Is there a work around this without changing data type of timestamp field. I already have a lot of data in this collection so don't want to go through the hassle of import/export, etc.

Also - I want to keep the load for sorting to mongodb rather than doing it programmatically in python.

To be clear: Timestamp does not indicate when the document was created and it is stored as string (e.g. "1404217646").

Thanks in advance.

Solution 1

Assuming your timestamp indicates when the document was created, you can use _id instead.

_id ObjectId in mongo stores your timestamp. Try the following:

sort = {'_id': -1}
collection.find({}, limit=10).sort(sort)

If you still want to sort by your custom timestamp field, the following should work:

sort = {'timestamp': -1}
collection.find({}, limit=10).sort(sort)

Note that this is assuming all of your timestamp fields are of the same type (string, int)

Solution 2

You can sort your collection in descending order by using sort( { 'timestamp': -1 } ).Your query will be like this

collection.find().sort( { 'timestamp': -1 } ).limit(10)

If you have sql knowledge, you can compare both queries in the following link

http://docs.mongodb.org/manual/reference/sql-comparison/

Comments

[Sammaye]

It should be noted that even though this works since it is a single field your are sorting by it is not the best way to do it in python since dicts are unordered

[Mulagala]

@Sammaye, I thought that there are multiple filed's and timestamp is a common field in the collection.

[Sammaye]

There are multiple fields in the documents themselves but within the sort specification you are only supplying one field.

[jisu]

Timestamp does not indicate when the document was created - so the above won't work. Aren't the two solutions same?

[jisu]

Isn't the suggested solution same as: collection.find(limit=10).sort( { 'timestamp': -1 } )

[Martin Konecny]

You're right - fixed. The second solution works? If not you may need to map your data to the same type. Theres a quick way for that

[jisu]

The second solution also doesn't work as timestamp is stored as a string. I have my sort var implemented as such: [('timestamp', -1)]. Anyway, I would be interested in knowing how can I "map data to the same type." The final solution, I am thinking, will be to reconstruct docs so all have timestamp stored in bson date format.

[Martin Konecny]

Depending on how your string is formatted, sorting can still work. One example is if the format is a variation of YYYY-MM-DD HH:MM where HH is in the range of [00, 23].

[a zEnItH]

I follow the same step but it returns TypeError: if no direction is specified, key_or_list must be an instance of a list, ``` >>> for x in post.find(): ... print(x['timestamp']) ... Timestamp(1591790235, 1) Timestamp(1591790259, 1) Timestamp(1591790271, 1) ```