[DOCS-10610] Docs for SERVER-6310: Timezone support in date operators at query time Created: 31/Jul/17  Updated: 29/Oct/23  Resolved: 21/Sep/17

Status: Closed
Project: Documentation
Component/s: None
Affects Version/s: None
Fix Version/s: 3.5.11

Type: Task Priority: Major - P3
Reporter: Emily Hall Assignee: Ravind Kumar (Inactive)
Resolution: Fixed Votes: 0
Labels: aggregation, expression, timezone, usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Documented
documents SERVER-6310 Timezone support in date operators at... Closed
Duplicate
duplicates DOCS-10525 Docs for SERVER-28611: Add the tzExpr... Closed
Related
is related to DOCS-10566 Docs for SERVER-29824: Add support fo... Closed
Participants:
Days since reply: 6 years, 21 weeks ago
Epic Link: DOCS: 3.6 Server

 Description   

Documentation Request Summary:

Design document can be found through https://docs.google.com/document/d/1I_x4NNWgfcXzmEFTPTYZkjIQVq_JUnZcxb5ak0o52oU/edit#heading=h.1rcba4503t2j

Engineering Ticket Description:

Issue Status as of July 26, 2017

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. MongoDB does not have any concept about a server-wide local time zone.

In versions 3.5.11 and newer, MongoDB still does not have a concept of a server-wide time zone, but 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.

USER IMPACT

This is not a BC breaking change. The original syntax of the operators is still functional:

> db.c.aggregate({hour: {$hour: '$lastUpdatedDate'}})

A new syntax is now also supported, to allow for the extra timezone field:

> db.c.aggregate({hour: {$hour: {date: '$lastUpdatedDate'}}})
> db.c.aggregate({hour: {$hour: {date: '$lastUpdatedDate', timezone: 'Europe/Amsterdam'}}})

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 from 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.



 Comments   
Comment by Kay Kim (Inactive) [ 21/Sep/17 ]

https://github.com/mongodb/docs/pull/2961

Generated at Thu Feb 08 08:00:59 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.