[SERVER-465] date matcher helpers Created: 09/Dec/09 Updated: 06/Dec/22 Resolved: 16/Mar/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Eliot Horowitz (Inactive) | Assignee: | Backlog - Query Team (Inactive) |
| Resolution: | Done | Votes: | 23 |
| Labels: | query_triage | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Assigned Teams: |
Query
|
||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
|
things like $month, $year, $curMonth, maybe
not sure yet |
| Comments |
| Comment by Ian Whalen (Inactive) [ 16/Mar/18 ] | |||||||||||
|
As per the above comments, this is now possible with the introduction of $expr in 3.6.0! | |||||||||||
| Comment by Asya Kamsky [ 09/Mar/18 ] | |||||||||||
|
To find all dates that are in a particular month (birthday view) and sort them:
| |||||||||||
| Comment by Asya Kamsky [ 09/Mar/18 ] | |||||||||||
|
With $expr added in 3.6 using date expressions available in aggregation these type of comparisons can now be done:
Starting with 3.7.3 (dev version) it's also possible to construct dates that represent "last day of previous month" for comparisons. | |||||||||||
| Comment by Keith Branton [ 22/Feb/11 ] | |||||||||||
|
@Chris, I didn't really consider index optimization because in my examples of social calendar people usually only have a few hundred friends at most, and, assuming lookup for the friends is indexed, further index optimization on the date is not really likely to help much. I'd have thought that if the date field was indexed then queries that could not be done with an index range scan would result in a full index scan. Either way scanning an index is way faster than scanning the collection. In my system I store birthdays as text strings YYYYMMDD and use regexes and ranges for the various queries I need. It works fine and performs well enough, except for the year-less birthday list. That's something I'd like to do. I'd just prefer that a date was actually stored as a date, and that we had a much more natural way to query them, and ideally sort on them (if sorting is in the scope of this change). | |||||||||||
| Comment by Chris Westin [ 22/Feb/11 ] | |||||||||||
|
Dates are currently stored as millisecond counts For the use case that Keith describes, the best thing to do is to handle the // create an index like this ); // save a person's profile }); // so that you can do this: ).explain(); ).explain(); } The alternative is to index a Date field; issuing a query like the above on db.profile2.ensureIndex( {birthDate:1}); // save a person's profile ); /* /* Eliot, what is the use case you have in mind? | |||||||||||
| Comment by Keith Branton [ 12/Jan/11 ] | |||||||||||
|
It would be really nice to have some date manipulation in the database. Working on social networks it is quite common to want: A list of all your friends in birthday order, irrespective of year of birth (could be sorted client side - but that would not support paging through a big cursor very well though. Some social networkers have thousands of "friends"). A list of birthdays coming up this week for emails like Facebook sends out (that's pretty easy to do client side by providing a range, but easier and "dry"-er if the db can do it). BirthdayAlarm.com displays a page like that, and uses oracle date functions to accomplish it. Being able to appropriately populate a month view calendar with friends birthdays - which requires finding all friends with birthdays in the given month irrespective if their year of birth. | |||||||||||
| Comment by Stephen Eley [ 06/Apr/10 ] | |||||||||||
|
This starts to feel a bit like feature bloat to me, and you'll begin to collide with different assumptions made about date calculation in different languages. (Both human and computer.) It's so simple to implement these with existing query operators that I'd rather see it done at the driver or framework level. |