Mongo group by Week is wrong time?

user_10787160 0
Viewed 276

I'm using aggregate with group to calculate max value of hours, days, or weeks. And everything is very well with hours, days. But have some issues with weeks group. For detail, I have a record of day: 2020-06-03 and that day belong to week 23 of 2020 ( week 23 from 2020-06-01 to 2020-06-07 ) but mongo is return the value of 2020-06-03 to week 22 of 2020

Sample: ( You can see at here: https://mongoplayground.net/p/T9LeA7jpNLa )

This is my mock recording:

[
  {
    "dateTime": ISODate("2020-06-03"),
    "value": 25
  },
  {
    "dateTime": ISODate("2020-05-25"),
    "value": 27
  }
]

And this is my query:

db.collection.aggregate([
  {
    "$match": {
      "dateTime": {
        "$gte": ISODate("2020-05-01"),
        "$lte": ISODate("2020-07-01")
      }
    }
  },
  {
    "$group": {
      "_id": {
        "week": {
          "$week": {
            "date": "$dateTime",
            "timezone": "+00:00"
          }
        },
        "year": {
          "$year": {
            "date": "$dateTime",
            "timezone": "+00:00"
          }
        }
      },
      "maxValue": {
        "$max": "$value"
      }
    }
  },
  {
    "$project": {
      "_id": {
        "start": {
          "$dateToString": {
            "date": {
              "$dateFromParts": {
                "isoWeekYear": "$_id.year",
                "isoWeek": "$_id.week"
              }
            },
            "format": "%Y-%m-%d"
          }
        },
        "end": {
          "$dateToString": {
            "date": {
              "$add": [
                {
                  "$dateFromParts": {
                    "isoWeekYear": "$_id.year",
                    "isoWeek": "$_id.week"
                  }
                },
                518400000
              ]
            },
            "format": "%Y-%m-%d"
          }
        }
      },
      "maxValue": "$maxValue"
    }
  }
])

And result:

[
  {
    "_id": {
      "end": "2020-05-24",
      "start": "2020-05-18"
    },
    "maxValue": 27
  },
  {
    "_id": {
      "end": "2020-05-31",
      "start": "2020-05-25"
    },
    "maxValue": 25
  }
]

My expect:

[
  {
    "_id": {
      "end": "2020-05-31",
      "start": "2020-05-25"
    },
    "maxValue": 27
  },
  {
    "_id": {
      "end": "2020-06-01",
      "start": "2020-06-07"
    },
    "maxValue": 25
  }
]
mongodb aggregate
2 Answers

From official document,

Returns the week of the year for a date 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.

the week number is zero-based instead of one-based that we used in common life.

So for your case in 2020, week 0 should be 2020-01-01 to 2020-01-05 and week 1 should be 2020-01-06 to 2020-01-12. This also explains for week 53, which is 2020-12-27 to 2020-12-31.

As identified by OP, the aggregation should be changed to use $isoWeek.

Mongo playground

user_147326690

The exactly answer is I used wrong operator. In $group stage, I used $week and $year but in $project stage I used $isoWeek and $isoWeekYear. It make conflict with the value #week of year, I need only be updated $week and $year in group stage to $isoWeek and $isoWeekYear.


db.collection.aggregate([
  {
    "$match": {
      "dateTime": {
        "$gte": ISODate("2020-05-01"),
        "$lte": ISODate("2020-07-01")
      }
    }
  },
  {
    "$group": {
      "_id": {
        "week": {
          "$isoWeek": {
            "date": "$dateTime",
            "timezone": "+00:00"
          }
        },
        "year": {
          "$isoWeekYear": {
            "date": "$dateTime",
            "timezone": "+00:00"
          }
        }
      },
      "maxValue": {
        "$max": "$value"
      }
    }
  },
  {
    "$project": {
      "_id": {
        "start": {
          "$dateToString": {
            "date": {
              "$dateFromParts": {
                "isoWeekYear": "$_id.year",
                "isoWeek": "$_id.week"
              }
            },
            "format": "%Y-%m-%d"
          }
        },
        "end": {
          "$dateToString": {
            "date": {
              "$add": [
                {
                  "$dateFromParts": {
                    "isoWeekYear": "$_id.year",
                    "isoWeek": "$_id.week"
                  }
                },
                518400000
              ]
            },
            "format": "%Y-%m-%d"
          }
        }
      },
      "maxValue": "$maxValue"
    }
  }
])db.collection.aggregate([
  {
    "$match": {
      "dateTime": {
        "$gte": ISODate("2020-05-01"),
        "$lte": ISODate("2020-07-01")
      }
    }
  },
  {
    "$group": {
      "_id": {
        "week": {
          "$week": {
            "date": "$dateTime",
            "timezone": "+00:00"
          }
        },
        "year": {
          "$year": {
            "date": "$dateTime",
            "timezone": "+00:00"
          }
        }
      },
      "maxValue": {
        "$max": "$value"
      }
    }
  },
  {
    "$project": {
      "_id": {
        "start": {
          "$dateToString": {
            "date": {
              "$dateFromParts": {
                "isoWeekYear": "$_id.year",
                "isoWeek": "$_id.week"
              }
            },
            "format": "%Y-%m-%d"
          }
        },
        "end": {
          "$dateToString": {
            "date": {
              "$add": [
                {
                  "$dateFromParts": {
                    "isoWeekYear": "$_id.year",
                    "isoWeek": "$_id.week"
                  }
                },
                518400000
              ]
            },
            "format": "%Y-%m-%d"
          }
        }
      },
      "maxValue": "$maxValue"
    }
  }
])

ref: https://mongoplayground.net/p/qTk3Opm9D3W

user_107871600
Related