Timezone with $dayOfYear on $group?

[Matias_Lopez]

Hi guys!

I’m trying to group documents with the operator $dayOfYear. Obviously, all dates are stored in UTC. The problem is if a document is recorded at 02/02/2021 02:00 AM UTC, in local time is 01/02/2021 11:00 PM, 4 hours of offset,

So, how I can handle this? We don’t want to store timezone on every document because two users can make the same query but on different timezones, independently where was created the record.

In short, how can set the timezone when using $dayOfYear with $group?

[Pavel_Duchovny]

Hi @Matias_Lopez,

Welcome back to MongoDB Community.

Why don’t you use the timezone in $dayOfYear syntax?

Example for GMT

[{$group: {
  _id: { "day" : {$dayOfYear : {"date" : "$saleDate",
    "timezone" : "GMT"
  }}},
  count: {
    $sum : 1
  }
}}]

Best regards,
Pavel

[Prasad_Saya]

Hello @Matias_Lopez, you can try this:

db.collection.aggregate([
  { 
    $group: { _id: { day_of_year_local: { $dayOfYear: { date: "$dateField", timezone: "-04:00" } } } }
  }
])

The timezone: "-04:00" is the -4 hours offset (or 4 hours behind UTC).

[Matias_Lopez]

Thanks @Pavel_Duchovny and @Prasad_Saya!