[SERVER-7695] Add variants of $week, $year, $dayOfWeek that conform to the ISO-6801 spec Created: 17/Nov/12  Updated: 15/Nov/21  Resolved: 29/Mar/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 2.1.2, 2.2.0, 2.2.1
Fix Version/s: 3.3.4

Type: Improvement Priority: Major - P3
Reporter: Ivan Bolgar Assignee: Charlie Swanson
Resolution: Done Votes: 12
Labels: expression
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by CSHARP-1623 Add variants of $week, $year, $dayOfW... Closed
Documented
is documented by DOCS-9548 Docs for SERVER-7695: Add variants of... Closed
Related
is related to DRIVERS-297 Aggregation Framework Support for 3.4 Closed
is related to SERVER-11118 Add dateToString aggregation operator Closed
Backwards Compatibility: Fully Compatible
Sprint: Query 12 (04/04/16)
Participants:

 Description   

We've decided to add the following operators:

  • $isoWeek
  • $isoDayOfWeek
  • $isoWeekYear

And to add the corresponding substitutions "%G", "%V", and "%u" to the $dateToString expression.

Example

Input

{_id: 0, date: ISODate("2016-01-01T00:00:00Z")}  // Thu, Jan 1, 2016

Pipeline

db.coll.aggregate([{
    $project: {
        dayOfWeek: {$dayOfWeek: "$date"},
        isoDayOfWeek: {$isoDayOfWeek: "$date"},
        year: {$year: "$date"},
        isoYear: {$isoWeekYear: "$date"},
        week: {$week: "$date"},
        isoWeek: {$isoWeek: "$date"},
        isoString: {$dateToString: {format: "%G-W%V-%u", date: "$date"}}
    }
}])

Output

{
  _id: 0,
  dayOfWeek: 6,  // 1 is Sunday, 7 is Saturday
  isoDayOfWeek: 5,  // 1 is Monday, 7 is Sunday
  year: 2016,
  isoYear: 2015,  // 2016 starts on Jan 4th under ISO calendar.
  week: 0,
  isoWeek: 53,
  isoString: "2015-W53-5"  // $dateToString is also modified to support
                           // the concepts above.
}

Additional Notes

Original Description

It would be very useful for $week to have an option to start from Monday. Get it from current locale, is first that came in my mind.



 Comments   
Comment by Githook User [ 29/Mar/16 ]

Author:

{u'username': u'jigfox', u'name': u'Jens Fahnenbruck', u'email': u'jigfox@me.com'}

Message: SERVER-7695 Add ISO versions of date expressions.

Implements expressions for dealing with week as described in the
ISO-8601 standard (https://en.wikipedia.org/wiki/ISO_week_date).

Also implements the %G, %V, and %u specifiers for $dateToString.

Closes #1068
Branch: master
https://github.com/mongodb/mongo/commit/ea07e34466f14b127ac97f58ec6a40e9e52ebbd5

Comment by Jens Fahnenbruck [ 10/Mar/16 ]

charlie.swanson, thanks for approving the new expression, I already added comments to the code to explain it a bit more, but as I mentioned on GitHub, I don't like reducing the coverage because of the complexity of the algorithms used.

Comment by Charlie Swanson [ 09/Mar/16 ]

jfahnenbruck, we've approved the new expressions, I'll look over your pull request now.

Comment by Ramon Fernandez Marina [ 01/Mar/16 ]

Thanks jfahnenbruck, and than you for taking the time to put together a pull request. Please continue to watch this ticket for updates.

Cheers,
Ramón.

Comment by Jens Fahnenbruck [ 01/Mar/16 ]

ramon.fernandez I've just signed the contributer agreement

Comment by Ramon Fernandez Marina [ 01/Mar/16 ]

jfahnenbruck, please note you'll need to sign the contributor agreement before we can consider your pull request; please drop us a note on this ticket when you've signed.

Thanks,
Ramón.

Comment by Charlie Swanson [ 29/Feb/16 ]

Hi jfahnenbruck,

Thank you for submitting a pull request! We are in the process of approving a list of new aggregation features to be worked on in the next couple of months. I'll add this ticket to the list. If we all agree that this is worth adding, I'll take a look at the pull request and we can try to get it merged!

I'll keep you posted with progress.

Comment by Jens Fahnenbruck [ 19/Feb/16 ]

this implements operators for dealing with week as described in the
ISO-8601 standard (https://en.wikipedia.org/wiki/ISO_week_date).

the implemented oprators are:

  • $isoWeek returns the ISO-8601 week number of the given date
  • $isoDayOfWeek returns the number of the day with the week from 1 for Monday to 7 for Sunday
  • $isoWeekYear returns the year of the ISO-8601 week of the date

example:

{ $isoWeek: ISODate("2016-01-01T00:00:00Z") }

returns 53

{ $isoDayOfWeek: ISODate("2016-01-01T00:00:00Z") }

returns 5

{ $isoWeekYear: ISODate("2016-01-01T00:00:00Z") }

returns 2015

this also implements the specifiers for $dateToString so it is possible
to create Dates defined by week like this:

    {
        $dateToString: {
            format: "%G-W%V-%u",
            date: ISODate("2016-01-01T00:00:00Z")
        }
    }

which will return "2015-W53-5"

Comment by Jens Fahnenbruck [ 19/Feb/16 ]

I just created a pull request: https://github.com/mongodb/mongo/pull/1068

Comment by Jens Fahnenbruck [ 19/Feb/16 ]

Hi Oleg,

thank you for your review offer, I'm currently working on creating the new Aggregators:

$isoWeek: return the week for iso8601 calendar (1-53)
$isoWeekYear, returns the year of the week (i.e. 2016-01-01 is calendar week 53 and the week year is 2015)
$isoDayOfWeek, returns the numbers 1 to 7 for Monday to Sunday

it's working, currently I'm writing some tests to verify the correctness of the code. It can take some time, because I'm doing this in my spare time.

I will also try to add %G for week year, %V for the iso week number and %u for the isoDayOfWeek to $dateToString so it will be possible to create a valid ISO 8601 date in the form of 2004-W28-7

Comment by Oleg Rekutin [ 18/Feb/16 ]

Jens, if you work on the pull request, I can help review your code. That will increase its chances of being accepted.

Comment by Jens Fahnenbruck [ 18/Feb/16 ]

I'd like to implement $isoWeek and $isoWeekYear, what chances would I have to get a pull request accepted if I succeed?

Comment by Vesa Poikajärvi [ 06/Mar/13 ]

And while at it one could implement also the third common option for week, i.e. week 1 is the week with Jan 1st.

When creating metrics from data in DB, like a line chart of count over time, it is quite common to use year/week on X axis. The current method for deciding week 1 is rarely useful (at least for me) because I need to use either ISO weeks (in most cases) or the American version (week 1 starts on Jan 1st).

Currently I need to use mapReduce which adds complexity over using aggregate to get correct week numbers for my data.

Comment by Andrey Khozov [ 26/Feb/13 ]

I think it would be useful if there was a new feature (eg $isoweek, as in postgre), so you can use $week and $isoweek in the same query.

Generated at Thu Feb 08 03:15:20 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.