How count e.g. ice days over month/years

Question

I have a weather station with data over 14 years every 10min: like this

_id:60fbcf880000000000000000  
datum:2021-07-24T08:30:00.000+00:00  
temperature:19.5

Now I want to count per year and per month certain days.

Ice days (temperature is a 24h always below 0°),
winter days (temperature is at a time under 0°)
cold days (max temp <10°C)
hot days (max temp 25-30°C)
very hot days (max temp over 30°).

I have no real clue about the best query code for mongo. I can group for days, but then I have the issue to count those certain days ($buckets?)

I come until the grouping:

{$group: {
  _id: [{$year: '$datum'},  <br>
  {$month: '$datum'},  <br>
  {$dayOfMonth: '$datum'}],  <br>
  temp_avg: {$avg: '$tempAussen'},  <br>
  temp_min: {$min: '$tempAussen'},  <br>
  temp_max: {$max: '$tempAussen'},  <br>
}}

Result in a list with elements like:

_id:Array
0:2020  
1:3
2:2 
temp_avg:6.12  
temp_min:0.7  
temp_max:9.6

But now starts my problem: How to count the days for (e.g. 2020 month 3) with temperature <0° and the other days?

have you try this aggregate https://stackoverflow.com/questions/27366209/mongodb-group-by-month — Bhavesh Chauhan, Jul 24 '21 at 09:26
it helps for the groups ... but it does not help for the counting of the days.. — GeorgB, Jul 24 '21 at 12:12

score 1 · Accepted Answer · answered Jul 25 '21 at 02:17

Your grouping is working fine. You just need one more extra $group by month to sum conditionally for each type of day. For example, you can do

{
    $group: {
      _id: {
        "year": "$_id.year",
        "month": "$_id.month",
        
      },
      winter_days_count: {
        $sum: {
          "$cond": {
            "if": {
              $lt: [
                "$temp_min",
                0
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      }
      ...

to count the winter days in the month.

Here is the Mongo playground for your reference.

How count e.g. ice days over month/years

1 Answers1