-
Type: Improvement
-
Resolution: Done
-
Priority: Minor - P4
-
Affects Version/s: 2.1.0, 2.1.1, 2.1.2
-
Component/s: Aggregation Framework
-
Fully Compatible
-
(copied to CRM)
ISSUE SUMMARY
In previous versions of MongoDB, the date extraction operations $year, $month, etc. could only extract the information from Date types in the UTC time zone. This meant that it was not possible to do grouping by date/time information in a local time zone. In versions 3.5.11 and newer, the date extraction operators ($year, $month, $day, $hour, $minute, $second, $millisecond, $isoYear, $isoWeekYear and $isoDayOfWeek) allow for a secondary format to specify a time zone to express a local time. This is accomplished with a new syntax in which the date operators accept an optional timezone argument in addition to the date argument:
> db.c.aggregate({hour: {$hour: {date: '$lastUpdatedDate'}}}) > db.c.aggregate({hour: {$hour: {date: '$lastUpdatedDate', timezone: 'Europe/Amsterdam'}}})
This does not break backwards compatibility with existing applications. The original syntax of the operators is still functional:
> db.c.aggregate({hour: {$hour: '$lastUpdatedDate'}})
TECHNICAL DETAILS
The implementation of time zone support is done through "timelib". Several formats for the timezone field are allowed:
- An "Olson Time Zone Idenfitier": America/New_York, Europe/London, etc.
- A string describing an ISO 8601 time zone specification: -05:00, +0445, -03, etc.
- The strings Z, UTC and GMT.
- Or an expression that evaluates to one of the above. This allows you to fetch the time zone information from a separate field in your document.
ADDITIONAL CHANGES
The $dateToString operator has also been updated (SERVER-28610), and the new operators $dateFromParts and $dateToParts (SERVER-28613), and $dateFromString (SERVER-29208, SERVER-29209) have been added, each also supporting time zones.
Original description
The date operators ($dayOfMonth, $dayOfWeek, $dayOfYear, $hour, $minute, $month, $week and $year) currently work for UTC. This causes the $group pipeline operator to insert certain rows in wrong bucket due to certain timezones being in another day.
As an example, say you're showing website statistics for a US based company and at December 31st 2011 at 10:00 PM EST someone finally visits the website, if the aggregation framework didn't respect timezones (like it does now) it'd group the data point the next day (since it's stored as 2012-01-01T02:00:00Z). Which, if you're grouping data by year is quite the error as it would look like you'd have visitors from the future.
Being able to set the timezone, either per query or per connection would solve this.
- is duplicated by
-
SERVER-25243 Timezone support in date-time aggregation functions
- Closed
-
SERVER-3882 aggregation: date extraction functions should have optional target time zone
- Closed
- is related to
-
RUBY-1464 Time offset ignored in aggregation queries when using ActiveSupport::TimeWithZone
- Closed
-
TOOLS-1589 A way to import with dates in local timezone
- Closed
-
SERVER-465 date matcher helpers
- Closed