Issue filtering date with Time Zone UTC-03:00 Brasilia

[michael.kz (Michael Marcel)]

Hello, I’m having problems filtering date using Atlas Charts.

Let me show you what I’m talking about…

Chart (Image)

I have chart above showing how many Gains and Losses we had today.
In my collection I have a field named “result” which is filled with “gain” or “loss”, and I’m using an aggregation count with this field.

But I need to show only documents which was inserted today, so I was filtering like this:

Filtering Date on the Chart (Image)

As we can see I have a field named “created_at” with the date, and I set the UTC Time Zone as UTC-03:00
I’m using python (pymongo) to fill my collections and to fill the “created_at” field I used the package datetime and function datetime.now()

I verified these fields and the date are correct and they’re using my local time (UTC-03:00 Brasilia)

created_at: 2022-12-10T00:25:23.273+00:00

PS: It’s a date field, not string field.

But, the problem is…

Today’s count is wrong on the chart! Counting manually we see that we have 26 LOSSES, but on the chart it appears 23
I used the following filter and count manually looking at the date:
Filtering my Collection to count manually (Image)

Probably the gain is incorrect too but I used losses for example to make it easier to count manually.

Is it a problem on the chart filtering date? What can I do to show only today’s document?

@edit:
If I change the filter to Absolute and fill with today’s date, it WORKS, but i don’t want to have to change the filter everyday.

[tomhollander]

Hi @michael.kz -

Can you clarify exactly how you are storing the dates? The MongoDB date type is not time zone-aware, so the correct way of storing dates is to always normalise them to UTC for storage, and then you can convert them to your desired time zone for display or analysis. You may be doing this already but it’s unclear from your post so I wanted to establish this before looking at other possible issues.

Tom

[tomhollander]

You may also be able to debug this somewhat by looking at the aggregation pipeline generated for the chart. The time boundaries are calculated externally to the pipeline by the Charts backend, but should give you an idea of what’s happening. Here I’m also using the Period / 1 day filter set to my time zone of UTC+11. You can see that the calculated date boundaries are at 13:00 UTC which is midnight in UTC+11.

[michael.kz (Michael Marcel)]

Oh, thank you Tom. I’m new using Atlas Charts, now I could understand what’s happening.

I was using the filter with UTC Time Zone 03:00 Brasilia, but looking at the Chart Aggregation Pipeline I saw it was filtering from 03:00 AM, but the date on the field is already using my correct local time.

[
  {
    "$match": {
      "created_at": {
        "$gte": {
          "$date": "2022-12-11T03:00:00Z"
        },
        "$lt": {
          "$date": "2022-12-12T03:00:00Z"
        }
      }
    }
  },
  {
    "$addFields": {
      "result": {
        "$switch": {
          "branches": [
            {
              "case": {
                "$in": [
                  {
                    "$toString": "$result"
                  },
                  [
                    {
                      "$literal": "green"
                    }
                  ]
                ]
              },
              "then": "Gain"
            },
            {
              "case": {
                "$in": [
                  {
                    "$toString": "$result"
                  },
                  [
                    {
                      "$literal": "loss"
                    }
                  ]
                ]
              },
              "then": "Loss"
            }
          ],
          "default": "Other values"
        }
      }
    }
  },
  {
    "$group": {
      "_id": {
        "__alias_0": "$result"
      },
      "__alias_1": {
        "$sum": {
          "$cond": [
            {
              "$ne": [
                {
                  "$type": "$result"
                },
                "missing"
              ]
            },
            1,
            0
          ]
        }
      },
      "__alias_2": {
        "$sum": {
          "$cond": [
            {
              "$ne": [
                {
                  "$type": "$result"
                },
                "missing"
              ]
            },
            1,
            0
          ]
        }
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "__alias_0": "$_id.__alias_0",
      "__alias_1": 1,
      "__alias_2": 1
    }
  },
  {
    "$project": {
      "color": "$__alias_1",
      "x": "$__alias_0",
      "y": "$__alias_2",
      "_id": 0
    }
  },
  {
    "$addFields": {
      "__agg_sum": {
        "$sum": [
          "$y"
        ]
      }
    }
  },
  {
    "$group": {
      "_id": {
        "x": "$x"
      },
      "__grouped_docs": {
        "$push": "$$ROOT"
      },
      "__agg_sum": {
        "$sum": "$__agg_sum"
      }
    }
  },
  {
    "$sort": {
      "__agg_sum": -1
    }
  },
  {
    "$unwind": "$__grouped_docs"
  },
  {
    "$replaceRoot": {
      "newRoot": "$__grouped_docs"
    }
  },
  {
    "$project": {
      "__agg_sum": 0
    }
  },
  {
    "$limit": 5000
  }
]

Removing the Time Zone filter it works!!!

[michael.kz (Michael Marcel)]

Oh I got another problem right now @ tomhollander, because I’m in Brazil, and here it’s 09:33 PM on the 11th yet, but my date filter is already using tomorrow’s date (12th). Probably because in UTC it’s already 12th. What can I do in this case?

[tomhollander]

The solution is to normalise your dates to UTC before they are stored. If you treat them as local, other date/time functions won’t work as expected as they assume your date is stored as UTC.

If you can’t change the stored dates, another possibility may be to create a calculated field that normalises the dates to proper UTC (i.e. subtract 3 hours) and then do the time zone-aware filtering on that field. This kind of calculation gets messy when DST is involved, but I see Brazil doesn’t observe DST so it may be a viable option.

Tom

[michael.kz (Michael Marcel)]

Thanks tom! I created a utc_date calculated field from my original created_at field converting to UTC, and changed the chart filter to UTC-03:00 Brasilia and now it’s working perfectly