Mongodb $week and Java Calendar.WEEK_OF_YEAR

Question

Takes a date and returns the week of the year as a number between 0 and 53.

Weeks begin on Sundays, and week 1 begins with the first Sunday of the year. Days 
preceding the first Sunday of the year are in week 0. This behavior is the same as the
 “%U” operator to the strftime standard library function.

whereas, Java Calendar's DAY_OF_WEEK returns slightly differently (US Locale). For instance, for 2013, mongo's week 1 is actually week 2.

My question is, adding arbitrary 1 does not solve the problem. Is there a formula I can use to determine which week number to use to get the week start date.

Scenario: I am running an aggregation in mongo which returns me a week number. Based on the week number, I need to arrive at week start date.

Would something like the following work always? Assume calendar is another instance of Calendar.

    Calendar firstDay = Calendar.getInstance(Locale.US);
    firstDay.set(Calendar.DAY_OF_YEAR, 1);
    int day = firstDay.get(Calendar.DAY_OF_WEEK);
    if (day != Calendar.SUNDAY){
        //both mongo and java calendar start in different weeks so add 1 to reconcile
        calendar.set(Calendar.WEEK_OF_YEAR, number.intValue()+1);
    }

You say "Based on the week number, I need to arrive at week start date." - starting on a Sunday? Monday? Or Locale-specific? — Trisha, Jul 05 '13 at 10:11
@Trisha In this case, I am looking at Sunday as first day of week. But a generic solution would be welcome. The problem I am trying to solve is specifically to US Locale. — Nasir, Jul 05 '13 at 15:13
I think I got a good answer here http://stackoverflow.com/a/30247836/2366413 — Roman Blachman, May 17 '15 at 11:21
@RomanBlachman: That's broadly equivalent to my answer - but using Joda Time instead of Calendar. — Jon Skeet, May 19 '15 at 13:46

Jon Skeet · Accepted Answer · 2015-05-15T10:55:45.500

I would avoid bringing the Java week-of-year into the picture at all - unless you can persuade MongoDB to stop being so broken, somehow. (That's a really odd definition of week-of-year; I'd normally use the ISO-8601 definition.)

If you really need to this with java.util.Calendar, you could probably use something like this (untested):

// You really want to do all of this on a "date" basis, without DST messing
// things up...
Calendar firstDay = Calendar.getInstance(TimeZone.getTimeZone("UTC"), Locale.US);
firstDay.set(Calendar.DAY_OF_YEAR, 1);
while (firstDay.get(Calendar.DAY_OF_WEEK) != Calendar.SUNDAY) {
    firstDay.add(Calendar.DAY, 1);
}

// firstDay is now at the start of week 1 from a mongodb perspective...
// We just need to add the number of weeks.
firstDay.add(Calendar.Day, (mongoWeekNumber - 1) * 7);

Note that if the week number is 0, the start of the week could easily be in a calendar year before the current one.

If you have any choice in which date/time API you use, I would strongly recommend that you use Joda Time or java.time instead of Calendar though - then you can work with LocalDate instead, and have a much saner time.

Trisha · Answer 2 · 2013-07-05T15:29:46.960

I've written a slightly different method, and tested it against every week in 2013 and 2014, and it seems to work:

private Date getStartingSundayDateForMongoWeek(int mongoWeekOfYear, int year) {
    Calendar calendar = Calendar.getInstance(US);
    calendar.set(Calendar.HOUR_OF_DAY, 0);
    calendar.set(Calendar.MINUTE, 0);
    calendar.set(Calendar.SECOND, 0);
    calendar.set(Calendar.MILLISECOND, 0);
    calendar.setWeekDate(year, mongoWeekOfYear + 1, Calendar.SUNDAY);
    return calendar.getTime();
}

I'm returning it as a Date for testing purposes, but obviously you can do what you need to it. It assumes you wanted Sunday as the start of the week, but again you can tweak this to Monday or whatever your requirements are.

I wrote a Spock test to check it because of Spock's amazing data-driven testing:

private static final SimpleDateFormat DATE_FORMAT_SHORT = new SimpleDateFormat("MMM dd yyyy", Locale.US)

@Unroll
def 'determine correct start of week as #startOfWeek from mongo week #mongoWeekOfYear'() {
    expect:
    startOfWeek == getStartingSundayDateForMongoWeek(mongoWeekOfYear, year)

    where:
    //taken from http://www.timeanddate.com/calendar/?year=2013&country=1
    startOfWeek                            | mongoWeekOfYear | year
    DATE_FORMAT_SHORT.parse("Dec 30 2012") | 0               | 2013
    DATE_FORMAT_SHORT.parse("Jan 6  2013") | 1               | 2013
    DATE_FORMAT_SHORT.parse("Jan 13 2013") | 2               | 2013
    DATE_FORMAT_SHORT.parse("Jan 20 2013") | 3               | 2013
    DATE_FORMAT_SHORT.parse("Jan 27 2013") | 4               | 2013
    DATE_FORMAT_SHORT.parse("Feb 3  2013") | 5               | 2013
    DATE_FORMAT_SHORT.parse("Feb 10 2013") | 6               | 2013
    DATE_FORMAT_SHORT.parse("Feb 17 2013") | 7               | 2013
    DATE_FORMAT_SHORT.parse("Feb 24 2013") | 8               | 2013
    DATE_FORMAT_SHORT.parse("Mar 3  2013") | 9               | 2013
    DATE_FORMAT_SHORT.parse("Mar 10 2013") | 10              | 2013
    DATE_FORMAT_SHORT.parse("Mar 17 2013") | 11              | 2013
    DATE_FORMAT_SHORT.parse("Mar 24 2013") | 12              | 2013
    DATE_FORMAT_SHORT.parse("Mar 31 2013") | 13              | 2013
    DATE_FORMAT_SHORT.parse("Apr 7  2013") | 14              | 2013
    DATE_FORMAT_SHORT.parse("Apr 14 2013") | 15              | 2013
    DATE_FORMAT_SHORT.parse("Apr 21 2013") | 16              | 2013
    DATE_FORMAT_SHORT.parse("Apr 28 2013") | 17              | 2013
    DATE_FORMAT_SHORT.parse("May 5  2013") | 18              | 2013
    DATE_FORMAT_SHORT.parse("May 12 2013") | 19              | 2013
    DATE_FORMAT_SHORT.parse("May 19 2013") | 20              | 2013
    DATE_FORMAT_SHORT.parse("May 26 2013") | 21              | 2013
    DATE_FORMAT_SHORT.parse("Jun 2  2013") | 22              | 2013
    DATE_FORMAT_SHORT.parse("Jun 9  2013") | 23              | 2013
    DATE_FORMAT_SHORT.parse("Jun 16 2013") | 24              | 2013
    DATE_FORMAT_SHORT.parse("Jun 23 2013") | 25              | 2013
    DATE_FORMAT_SHORT.parse("Jun 30 2013") | 26              | 2013
    DATE_FORMAT_SHORT.parse("Jul 7  2013") | 27              | 2013
    DATE_FORMAT_SHORT.parse("Jul 14 2013") | 28              | 2013
    DATE_FORMAT_SHORT.parse("Jul 21 2013") | 29              | 2013
    DATE_FORMAT_SHORT.parse("Jul 28 2013") | 30              | 2013
    DATE_FORMAT_SHORT.parse("Aug 4  2013") | 31              | 2013
    DATE_FORMAT_SHORT.parse("Aug 11 2013") | 32              | 2013
    DATE_FORMAT_SHORT.parse("Aug 18 2013") | 33              | 2013
    DATE_FORMAT_SHORT.parse("Aug 25 2013") | 34              | 2013
    DATE_FORMAT_SHORT.parse("Sep 1  2013") | 35              | 2013
    DATE_FORMAT_SHORT.parse("Sep 8  2013") | 36              | 2013
    DATE_FORMAT_SHORT.parse("Sep 15 2013") | 37              | 2013
    DATE_FORMAT_SHORT.parse("Sep 22 2013") | 38              | 2013
    DATE_FORMAT_SHORT.parse("Sep 29 2013") | 39              | 2013
    DATE_FORMAT_SHORT.parse("Oct 6  2013") | 40              | 2013
    DATE_FORMAT_SHORT.parse("Oct 13 2013") | 41              | 2013
    DATE_FORMAT_SHORT.parse("Oct 20 2013") | 42              | 2013
    DATE_FORMAT_SHORT.parse("Oct 27 2013") | 43              | 2013
    DATE_FORMAT_SHORT.parse("Nov 3  2013") | 44              | 2013
    DATE_FORMAT_SHORT.parse("Nov 10 2013") | 45              | 2013
    DATE_FORMAT_SHORT.parse("Nov 17 2013") | 46              | 2013
    DATE_FORMAT_SHORT.parse("Nov 24 2013") | 47              | 2013
    DATE_FORMAT_SHORT.parse("Dec 1  2013") | 48              | 2013
    DATE_FORMAT_SHORT.parse("Dec 8  2013") | 49              | 2013
    DATE_FORMAT_SHORT.parse("Dec 15 2013") | 50              | 2013
    DATE_FORMAT_SHORT.parse("Dec 22 2013") | 51              | 2013
    DATE_FORMAT_SHORT.parse("Dec 29 2013") | 0               | 2014
    DATE_FORMAT_SHORT.parse("Jan 5  2014") | 1               | 2014
    DATE_FORMAT_SHORT.parse("Jan 12 2014") | 2               | 2014
    DATE_FORMAT_SHORT.parse("Jan 19 2014") | 3               | 2014
    DATE_FORMAT_SHORT.parse("Jan 26 2014") | 4               | 2014
    DATE_FORMAT_SHORT.parse("Feb 2 2014")  | 5               | 2014
    DATE_FORMAT_SHORT.parse("Feb 9 2014")  | 6               | 2014
    DATE_FORMAT_SHORT.parse("Feb 16 2014") | 7               | 2014
    DATE_FORMAT_SHORT.parse("Feb 23 2014") | 8               | 2014
    DATE_FORMAT_SHORT.parse("Mar 2 2014")  | 9               | 2014
    DATE_FORMAT_SHORT.parse("Mar 9 2014")  | 10              | 2014
    DATE_FORMAT_SHORT.parse("Mar 16 2014") | 11              | 2014
    DATE_FORMAT_SHORT.parse("Mar 23 2014") | 12              | 2014
    DATE_FORMAT_SHORT.parse("Mar 30 2014") | 13              | 2014
    DATE_FORMAT_SHORT.parse("Apr 6 2014")  | 14              | 2014
    DATE_FORMAT_SHORT.parse("Apr 13 2014") | 15              | 2014
    DATE_FORMAT_SHORT.parse("Apr 20 2014") | 16              | 2014
    DATE_FORMAT_SHORT.parse("Apr 27 2014") | 17              | 2014
    DATE_FORMAT_SHORT.parse("May 4 2014")  | 18              | 2014
    DATE_FORMAT_SHORT.parse("May 11 2014") | 19              | 2014
    DATE_FORMAT_SHORT.parse("May 18 2014") | 20              | 2014
    DATE_FORMAT_SHORT.parse("May 25 2014") | 21              | 2014
    DATE_FORMAT_SHORT.parse("Jun 1 2014")  | 22              | 2014
    DATE_FORMAT_SHORT.parse("Jun 8 2014")  | 23              | 2014
    DATE_FORMAT_SHORT.parse("Jun 15 2014") | 24              | 2014
    DATE_FORMAT_SHORT.parse("Jun 22 2014") | 25              | 2014
    DATE_FORMAT_SHORT.parse("Jun 29 2014") | 26              | 2014
    DATE_FORMAT_SHORT.parse("Jul 6 2014")  | 27              | 2014
    DATE_FORMAT_SHORT.parse("Jul 13 2014") | 28              | 2014
    DATE_FORMAT_SHORT.parse("Jul 20 2014") | 29              | 2014
    DATE_FORMAT_SHORT.parse("Jul 27 2014") | 30              | 2014
    DATE_FORMAT_SHORT.parse("Aug 3 2014")  | 31              | 2014
    DATE_FORMAT_SHORT.parse("Aug 10 2014") | 32              | 2014
    DATE_FORMAT_SHORT.parse("Aug 17 2014") | 33              | 2014
    DATE_FORMAT_SHORT.parse("Aug 24 2014") | 34              | 2014
    DATE_FORMAT_SHORT.parse("Aug 31 2014") | 35              | 2014
    DATE_FORMAT_SHORT.parse("Sep 7 2014")  | 36              | 2014
    DATE_FORMAT_SHORT.parse("Sep 14 2014") | 37              | 2014
    DATE_FORMAT_SHORT.parse("Sep 21 2014") | 38              | 2014
    DATE_FORMAT_SHORT.parse("Sep 28 2014") | 39              | 2014
    DATE_FORMAT_SHORT.parse("Oct 5 2014")  | 40              | 2014
    DATE_FORMAT_SHORT.parse("Oct 12 2014") | 41              | 2014
    DATE_FORMAT_SHORT.parse("Oct 19 2014") | 42              | 2014
    DATE_FORMAT_SHORT.parse("Oct 26 2014") | 43              | 2014
    DATE_FORMAT_SHORT.parse("Nov 2 2014")  | 44              | 2014
    DATE_FORMAT_SHORT.parse("Nov 9 2014")  | 45              | 2014
    DATE_FORMAT_SHORT.parse("Nov 16 2014") | 46              | 2014
    DATE_FORMAT_SHORT.parse("Nov 23 2014") | 47              | 2014
    DATE_FORMAT_SHORT.parse("Nov 30 2014") | 48              | 2014
    DATE_FORMAT_SHORT.parse("Dec 7 2014")  | 49              | 2014
    DATE_FORMAT_SHORT.parse("Dec 14 2014") | 50              | 2014
    DATE_FORMAT_SHORT.parse("Dec 21 2014") | 51              | 2014
    DATE_FORMAT_SHORT.parse("Dec 28 2014") | 0               | 2015

    DATE_FORMAT_SHORT.parse("Jan 1  2017") | 0               | 2017
    DATE_FORMAT_SHORT.parse("Jan 8  2017") | 1               | 2017
    DATE_FORMAT_SHORT.parse("Jan 15 2017") | 2               | 2017
    DATE_FORMAT_SHORT.parse("Jan 22 2017") | 3               | 2017
    DATE_FORMAT_SHORT.parse("Jan 29 2017") | 4               | 2017
    DATE_FORMAT_SHORT.parse("Feb 5  2017") | 5               | 2017
    DATE_FORMAT_SHORT.parse("Feb 12 2017") | 6               | 2017
    DATE_FORMAT_SHORT.parse("Feb 19 2017") | 7               | 2017
    DATE_FORMAT_SHORT.parse("Feb 26 2017") | 8               | 2017
}

Thanks. I haven't run the tests, but judging from the code, it appears in Jan 1, 2017 mongoweek will be same as calendar week, so all weeks will shift by 1 because of +1 - so weeks in 2017 will be incorrect. (perhaps, you could do a quick check. Doing Jan 1 2017, and Jan 15 2017should be sufficient.) — Nasir, Jul 05 '13 at 15:14
For your test data - Jan 1 2017, mongo column should be 1 not 0. Since Jan 1 is first Sunday of the year, week is 1 (based on mongo spec for $week). I expect now you will see the difference :). Similarly, other numbers should be higher by 1 (for 2017 only) — Nasir, Jul 05 '13 at 18:57
Not according to your question "Takes a date and returns the week of the year as a number between 0 and 53." So I'm inputting the mongo year as a number between 0 and 53, and the function returns the date of the first day of that week. — Trisha, Jul 06 '13 at 08:34
The next line reads, returns 0 for the days which precede the first Sunday of the year. So in 2017, there will be no week 0. Since first day of the year is also a Sunday, hence 1 is returned. — Nasir, Jul 07 '13 at 11:14
Sorry, I think it's not clear what the Spock test is showing - it states that you input a 0 for the mongo year for the year 2017, and you get "Jan 1 2017" as the output, the date of the first day of the week. I was assuming that's what you needed. — Trisha, Jul 07 '13 at 13:12
for the following ISODate, mongodb $week operator: `db.test.aggregate({$project: {week: {$week:{$literal: ISODate("2017-01-01T00:00:00.000Z") } } } })` the returned week number is: 1. when calling the getStartingSundayDateForMongoWeek(1,2017) the resulting Date() is "Sun Jan 01 00:00:00 IST 2017" - which is incorrect. — Roman Blachman, May 14 '15 at 14:23

Mongodb $week and Java Calendar.WEEK_OF_YEAR

2 Answers2