Working with timezones and $dateTrunc

J_D · 2024-04-26T21:33:35.000Z

I am confused about how $dateTrunc works. For example, consider the following:

`
ISODate(“2024-04-24T01:00:00Z”)

$dateTrunc: {
  date: "$date",
  unit: "hour",
  binSize: 2,
  timezone: "+02:00",
  startOfWeek: "Monday"
}

`

In this case, I get the result: 2024-04-24T00:00:00Z, which is correct. However, when I use the same input and corresponding timezone:

 $dateTrunc: {
      date: "$date",
      unit: "hour",
      binSize: 2,
      timezone: "Europe/Warsaw",
      startOfWeek: "Monday"
    }

I get 2024-04-24T01:00:00Z (where I should get the same result).

What is happening?

turivishal · 2024-04-27T04:39:36.843Z

Hi @J_D,

The Timezone Europe/Warsaw considers UTC+01:00 offset as per the link,

en.wikipedia.org

List of tz database time zones

This is a list of time zones from release 2024a of the tz database. Standard Time (STD) and Daylight Saving Time (DST) offsets from UTC in hours and minutes. For zones in which Daylight Saving is not observed, the DST offset shown in this table is a simple duplication of the STD offset. The UTC offsets are based on the current or upcoming database rules. This table does not attempt to document any of the historical data which resides in the database. Time zone abbreviations for both Standard ...

You can also refer to this $dateTrunc operator documentation has mentioned about timezone,

The timezone for the $dateTrunc calculation, specified as an expression that must resolve to a string that contains one of these values:

Olson Timezone Identifier

UTC Offset

If no timezone is provided, the $dateTrunc calculation is performed in UTC.

J_D · 2024-04-27T07:35:38.288Z

Thanks for your response. While Europe/Warsaw does use UTC+01:00 during standard time, it switches to UTC+02:00 during daylight saving time (DST). Since the example date I provided (2024-04-24T01:00:00Z) falls within the DST period, MongoDB should correctly apply the UTC+02:00 offset when using the “Europe/Warsaw” timezone name, giving the same result as using the explicit “+02:00” offset.

J_D · 2024-04-27T07:54:34.675Z

There is a small note for $dateSubstract:

When using an Olson Timezone Identifier in the field, MongoDB applies the DST offset if applicable for the specified timezone.

Not sure if this also applies to $dateTrunc