2

Is it possible to sort MongoDB's date values with a granularity?

I'd like to order my results by date, but I don't care about the hour/minute/second data. I'd prefer not to have to store an additional parameter like '20101215'.

ceejayoz
  • 176,543
  • 40
  • 303
  • 368
  • 1
    If you 'don't care about it' why does it matter if it uses the full date when sorting? The results are still sorted by Date when they are sorted by DateTime surely. – Ian Mercer Dec 16 '10 at 06:07
  • 1
    @Hightechrider The usage case is for a news website. I store the creation and update times for the news items, but the front page of the site needs to be sorted by the day (i.e. 2010-12-17), then by other factors. If I sort by exact timestamp, all the other factors are meaningless, as each item has a different hour/minute/second within that date. In MySQL, I'd do something along the lines of `ORDER BY DATE(created), priority`. – ceejayoz Dec 17 '10 at 13:58

1 Answers1

1

The short answer to your question is "No". It's not possible without storing another field. Even if it were possible you wouldn't want to b/c when sorting in Mongo you really need to index the fields you are sorting by, otherwise the db will need to scan every document in the db to do the sort. So if you were to do what you are suggesting you would also need to index on the partial date field which is also not possible in mongo.

I think the best approach would be to store 2 fields, one for the year/month/day and another for the hour/min/secs. Then you'll have more flexibility with sorting and indexing. It's not as clean or ideal as you'd probably like, but that's the only option with the db.

The other option would be to refine the order in your application after the results come back.

sym3tri
  • 3,747
  • 1
  • 29
  • 25