[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:
Depends
depends on SERVER-28611 Add the tzExpression argument to the ... Closed
Related
related to SERVER-6310 Timezone support in date operators at... Closed
Assigned Teams:
Query
Participants:

 Description   

things like $month, $year, $curMonth,
some way to do previous month, etc...

maybe

{ date : { $year : 2009 , $month : 10 } )

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:

db.birthdays.aggregate([
  /* match birthdays in June (6) */
   {$match:{$expr:{$eq:[6,{$month:"$db"}]}}},
  /* construct the date this year for the June birthday */
   {$addFields:{birthdayThisMonth:{$dateFromParts:{year:2018, month:{$month:"$db"},day:{$dayOfMonth:"$db"}}}}},
  /* sort by birthday this year */
   {$sort:{birthdayThisMonth:1}}
])
{ "_id" : ObjectId("5aa2db5594fb72e37873fb6b"), "db" : ISODate("1936-06-04T04:00:00Z"), "birthdayThisMonth" : ISODate("2018-06-04T00:00:00Z") }
{ "_id" : ObjectId("5aa2db4f94fb72e37873fb6a"), "db" : ISODate("1965-06-12T04:00:00Z"), "birthdayThisMonth" : ISODate("2018-06-12T00:00:00Z") }
{ "_id" : ObjectId("5aa2db5e94fb72e37873fb6c"), "db" : ISODate("1962-06-21T04:00:00Z"), "birthdayThisMonth" : ISODate("2018-06-21T00:00:00Z") }

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:

db.coll.find(find({$expr:{$eq:[1,{$month:"$d"}]}})
{ "_id" : ObjectId("5a5f8b24c22e16a81e507ad8"), "d" : ISODate("1970-01-01T00:00:00Z") }
{ "_id" : ObjectId("5a5f8b27c22e16a81e507ad9"), "d" : ISODate("2018-01-17T17:43:03.022Z") }
db.coll.find({$expr:{$gte:[{$hour:"$d"},17]}})
{ "_id" : ObjectId("5a5f8b27c22e16a81e507ad9"), "d" : ISODate("2018-01-17T17:43:03.022Z") }
db.coll.find({$expr:{$gte:[{$hour:{date:"$d",timezone:"-0400"}},17]}})
{ "_id" : ObjectId("5a5f8b24c22e16a81e507ad8"), "d" : ISODate("1970-01-01T00:00:00Z") }

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
(see http://bsonspec.org/#/specification), so indexing them for searching
purposes isn't great unless you're going to search for ranges specified that
way.

For the use case that Keith describes, the best thing to do is to handle the
component parts yourself:

// create an index like this
db.profile.ensureIndex(

{"birthDate.month":1, "birthDate.day":1}

);

// save a person's profile
db.profile.save({
firstName:"Fred",
lastName:"Flintstone",
birthDate:

{ year: 1963, month: 2, day: 17}

});

// so that you can do this:
db.profile.find(

{"birthDate.month":2, "birthDate.day":17}

).explain();
> db.profile.find(

{"birthDate.month":2, "birthDate.day":17}

).explain();
{
"cursor" : "BtreeCursor birthDate.month_1_birthDate.day_1",
"nscanned" : 1,
"nscannedObjects" : 1,
"n" : 1,
"millis" : 3,
"nYields" : 0,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" :

{ "birthdate.month" : [ [ 2, 2 ] ], "birthdate.day" : [ [ 17, 17 ] ] }

}

The alternative is to index a Date field; issuing a query like the above on
that will require computing the millisecond value of the beginning and end
of the date range and querying over that:

db.profile2.ensureIndex(

{birthDate:1}

);

// save a person's profile
db.profile2.save(

{ firstName:"Fred", lastName:"Flintstone", birthDate: new Date( /* year */1963, /* zero-based-month */1, /* one-based-day */ 17, /* minutes */ 0, /* seconds */ 0, /* milliseconds */ 0) }

);

/*
But to protect ourselves against future use of other sub-components, we
should really do the birthday query as
*/
db.profile2.find({birthDate:
{'$gte': new Date(1963, 1, 17, 0, 0, 0),
'$lt': new Date(1963, 1, 18, 0, 0, 0)}});

/*
But this is painful to formulate, especially for dates on the last day of
the month or year, where you have to adjust the other date fields for the
upper bound. It also doesn't handle Keith's requirement to ignore the year.
Given the storage format, that's not practical with a native Date.
*/

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.

Generated at Thu Feb 08 02:54:12 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.