[SERVER-6310] Timezone support in date operators at query time Created: 05/Jul/12 Updated: 16/Nov/21 Resolved: 26/Jul/17 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | 2.1.0, 2.1.1, 2.1.2 |
| Fix Version/s: | 3.5.11 |
| Type: | Improvement | Priority: | Minor - P4 |
| Reporter: | Gijs Kunze | Assignee: | Derick Rethans |
| Resolution: | Done | Votes: | 110 |
| Labels: | aggregation, expression, timezone, usability | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||||||||||||||||||
| Participants: |
Alex Paransky, Andrew Dowds, Bodenhaltung [X], Derick Rethans, Gerald Dalum, Gijs Kunze, Gordon Myers, Gustavo Manolo, Jarrah Watson, Jens Fahnenbruck, Joe Davis, Joe Esposito, Jonas, Ken Williams, Kishore Maley, Mathias Stearn, Matt Johnson, Paul Grinchenko, Philip Bergqvist, Pierre Bazoge, Ricardo Montoya, Robin Thomas, Roman Kisilenko, Ry Walker, Shaun, Stuart Hall, TP Diffenbach, Vasily Khayrulin, Ygor Lemos
|
||||||||||||||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||||||||||||||
| 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. 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:
This does not break backwards compatibility with existing applications. The original syntax of the operators is still functional:
TECHNICAL DETAILS The implementation of time zone support is done through "timelib". Several formats for the timezone field are allowed:
ADDITIONAL CHANGES The $dateToString operator has also been updated ( Original descriptionThe 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 Derick Rethans [ 24/Jul/17 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
This is related to | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Bodenhaltung [X] [ 30/Sep/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
+1 too. Is there a solution without $substraction the timezone offset from usec for aggregate with group? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Philip Bergqvist [ 02/Sep/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
For those of you working with mongodb in node, I've created a pretty simple (< 50 loc) helper function that generates a $cond expression to project times from UTC to a local timezone. It correctly takes daylight savings time (and other timezone offset changes) into consideration. You can find it on github here: https://github.com/pqvst/tzoffset | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ygor Lemos [ 31/Aug/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
+1000. This is a must have for generating time buckets using aggregations and dealing with dates properly on the database. Today we have to defer all those transformations to the application itself which makes it more complex and error prone since we need to do it everywhere and every time we deal with Date types. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Alex Paransky [ 31/Aug/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
By the way, if anyone from the MongoDB team looks at this issue and thinks that the TimeZone conversion should happen in the client they would be wrong. The result of the aggregation is physically different depending from what TimeZone the request is made. There is no way, UI can adjust for how the aggregation was bucketed on the server. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Roman Kisilenko [ 31/Aug/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
+1. It seems to me as well that this is a very important feature for a database. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gerald Dalum [ 31/Aug/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
Seems they are not going to change it. Best to move to another database if On Wed, Aug 31, 2016 at 3:42 PM, Philip Bergqvist (JIRA) <jira@mongodb.org> – | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Philip Bergqvist [ 31/Aug/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
It's very worrying that nothing has happened with this. It would be great to know why this is such a difficult problem to solve - or why it is considered unimportant. This just seems like a CRUCIAL feature that a general purpose database should have... | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gerald Dalum [ 08/Apr/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
this issue forced me to switch databases to postgres as my app had a lot of On Fri, Apr 8, 2016 at 2:48 PM, Alex Paransky (JIRA) <jira@mongodb.org> – | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Alex Paransky [ 08/Apr/16 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
We just ran into this issue. Stuart's comment hits the nail on the head:
We are doing something similar with the following projection:
However to do this correctly, we need to know which offset to use. To calculate the offset, we need to: 1) Query the first row of the result set The issue comes up when the data spans DST. This means that the offset cannot be calculated statically and needs to be calculated dynamically based on each date in the result set. It would be great if there was a feature in MongoDB to convert date into local date based on some timeZone without having to deal with manually adding offsets. I believe that this is an issue only with aggregations when crossing DST. If it was not for DST an offset solution would work fine. Because this only concerns aggregations, the feature should be added to the aggregation pipeline. The best way to implement this is to create a function called $localDate. For example:
Should convert the UTC date stored in $someTimestampField into the local date based on the 'America/Los_Angeles'. Note that the date should be properly converted relative to DST into either PST or PDT (that would be the whole point of this conversion). Afterwards, other date functions can be applied to the result to further break down the Date into it's specific constituents for aggregation and grouping. Timezone should not be set per connection or per query as at the connection level, it would cause issues for pooled connections and in the same query different dates could require different treatment. This is a pretty serious issue as short of re-implementing aggregation in code, or using slower map-reduce in favor of aggregation pipeline are non-trivial changes to existing code. So, I don't agree with priority of this being MINOR. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Stuart Hall [ 18/Sep/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
I can't state strongly enough how vital this functionality is to systems that cross multiple timezones. Yes, we could add multiple ISODate fields to support multiple timezones, but we frequently don't know all of the supported timezones at point of writing the data, so it's not always possible. It's a little frustrating when humble MySQL supports this, but MongoDB doesn't and it's a very hard question to answer when pressed by management about problems that arise as a result of this: (Note: the various solutions for applying manual offsets are ok, but don't take into account DST across long time-range queries. We have to apply some /very/ convoluted logic to handle this and it's clearly neither performant nor easy to support as a result) | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Pierre Bazoge [ 07/Aug/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
+1 The strengh of MongoDB for SaaS products is to provide analytics easily via the aggregate command and timezone support is CRITICAL for all global products. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Vasily Khayrulin [ 25/Jun/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
Gustavo, as a workaround you can use manual $add for timezone offset like in comments above | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gustavo Manolo [ 25/Jun/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
Any news about this? i've the same problem and i don't know how to fix it. I'm getting the results in UTC and i need to get the $group by results in $aggregate in the local timezone. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Jens Fahnenbruck [ 09/Jun/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
Anything new here? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gerald Dalum [ 13/May/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
Kishore, I also tried using the TZ offset but if you are trying to group data in the past, time zones mess around and get inconsistant data. What I had to do was create a DST table using moment-timezone and then determine the DST using a $cond with this table so the offset will be correct. It is an ugly hack but it works, so I am hoping mongo will step up to the plate on this and incorporate this for us. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Kishore Maley [ 13/May/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
This is important feature required, otherwise the heavy work of grouping done on database has no meaning... We overcome this issue by add TZ offset in the query | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Shaun [ 13/May/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
Facing the same issue here. Any word? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gerald Dalum [ 06/Mar/15 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
I am running into this issue also. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gordon Myers [ 26/Nov/14 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
I'm also wondering what the status of this is. It was first request 2½ years ago, and the last comment from anyone on the Mongo team is from almost a year ago. Does Mongo have any plans to introduce timezone support? I really don't think this should be classified as a "minor" priority item; this is critical for anyone wanting to develop enterprise applications. Doing conditional adds is hacky and not practical for any business application. We need true timezone support like any professional database would provide. Even MySQL has timezone support with the CONVERT_TZ function. Will Mongo offer any sort of similar operator for converting a UTC date to a given timezone, and if so, what's a realistic estimate for when that will be introduced? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Joe Esposito [ 17/Apr/14 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
We're aggregating across DST, so the offset workaround won't work. Are there any known workarounds for DST? Or, more practically, is this issue being looked at anytime soon? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mathias Stearn [ 07/Mar/14 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
mogarick, It could by changing the offset based on whether it is in DST or not. However, depending on how accurate you need to be, it could get very complex. For example, are you willing to use the current DST rules or do you need to handle historic dates with the then current rules. This is part of the reason that we don't have any built-in TZ support: a complete solution is extremely complicated and requires a way to update the tables as the rules change often. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ricardo Montoya [ 07/Mar/14 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
redbeard0531 I assume the offset workaround doesn't work for daylight saving. does it? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mathias Stearn [ 25/Sep/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
andrewdowds Yes it will. The full, unambiguous syntax for "regular" agg expressions is {$opName: [arg1, arg2, ...]}. As an convenience you are allowed to omit the wrapping array when passing a single argument, but the wrapping is always allowed. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Andrew Dowds [ 25/Sep/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
@Mathias Stearn, Will passing an array to $dayOfWeek to achieve the desired result continue to work when 9289 hits 2.6? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mathias Stearn [ 25/Sep/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
andrewdowds, My apologies. That is due to a bug ( As a work around, you can wrap the $add in an array like the following example. In 2.2 and 2.4 this is necessary when passing anything that looks like an object as the only parameter to an expression.
| |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Andrew Dowds [ 25/Sep/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
@Mathias Stearn, I attempted to use your example above on mongodb 2.4.3: {$dayOfWeek:{$add:['$date', '$tzOffsetMs']}} When I attempt to use the $add operator within the context of a date operator within an aggregation query, I get an error. Below is the query as formatted by pymongo and the resulting error: OperationFailure: command SON([('aggregate', u'donation_s'), ('pipeline', [{'$match': {'proj_id': ObjectId('redacted'), 'org_id': ObjectId('redacted')}}, {'$group': {'_id': {'day_r': {'$dayOfMonth': {'$add': ['$date_created', -14400000.0]}}, 'mo_r': {'$month': {'$add': ['$date_created', -14400000.0]}}, 'year_r': {'$year': {'$add': ['$date_created', -14400000.0]}}}, 'donations': {'$sum': '$amount'}}}, {'$sort': {'_id': 1}}])]) failed: exception: the $dayOfMonth operator does not accept an object as an operand Can you or someone verify that this capability actually exists in mongodb 2.4.x? Am I using it correctly? Thanks, | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Mathias Stearn [ 17/Sep/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
While not a 100% solution for all use cases, as of 2.4, you can now $add your timezone offset in milliseconds to a date to adjust it. This can be a fixed offset hard coded in the aggregation or be fetched from another field. For example, if you wanted to know the weekday on an event in the users' timezone you could do:
or even:
| |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Matt Johnson [ 18/Aug/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
This was recently discussed on StackOverflow here: http://stackoverflow.com/q/18287493/634824 Also to note, RavenDB can do this, and has a good writeup of the problem description here: http://ravendb.net/kb/61/working-with-date-and-time-in-ravendb#time-zone-conversions and there's another implementation that uses IANA standard time zones here: https://github.com/mj1856/RavenDB-NodaTime/wiki/Indexing-and-Querying I am not fully up to speed on the MongoDB aggregation framework, but there would have to be some kind of timezone conversion methods available in order to achieve similar results. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Jarrah Watson [ 08/Jul/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
just adding my voice to this cause - though it doesnt seem like there is any action from 10gen? same use case: our data is stored in UTC time users need to aggregate according to their local timezone. basically we need to pass timezone info along with each operator rather than assuming the zone of the data. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ken Williams [ 10/Jun/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
I'm running into this pretty badly too. The use case is basically the same - client's data is given originally in their local timezone. I can of course convert it to UTC for storage, but the problem is that I need to find aggregates of the data by local timezone semantics. Like @Robin says, the required timezone isn't the server's timezone, or the querier's timezone, it's at the very least intrinsic to the data. Ideally it should also be specifiable at query time. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gijs Kunze [ 20/Feb/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
My problem, as stated at the top, is with grouping by days. My current solution is to instead of grouping by $year, $month and $dayOfMonth I group by those values and add $hour to that, then after getting the results I parse those 4 values as being a UTC date, convert it to the desired timezone and generate the year, month and day of month values that way, afterwards I just have to manually merge the results. This means you get up to 24 times the amount of results and per returned item need to perform up to 24 merges but for small enough result sets this is doable. One issue with this approach is that it doesn't work with certain timezones which are not 60-minute offsets from UTC, there are some 30-minute and 15-minute offsets in the world. However this would lead to a small enough error that it's acceptable in our use case. Still hope we'll get a better solution for this soon(ish) but for now this approach works. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Robin Thomas [ 01/Feb/13 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
We're also a paid support customer, and we'd really like this feature. In fact, we need a full timezone conversion feature, not just the ability to use localtime() for whatever the mongo server's TZ setting is. I like TP Diffenbach's idea of '$dayOfYear': ['$aDate', true ], but in order to allow conversion to an arbitrary timezone, I suggest '$dayOfYear': ['$aDate', 'US/Eastern']. If the second arg is present, and is a string, look up in a cached zoneinfo db, and if it's a legit timezone, use it to make the tm struct. Perhaps boost::date_time library could be integrated to get zoneinfo db support and time->tm conversion? | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Paul Grinchenko [ 06/Dec/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
We really need this to be added to Aggregation Framework (we are on paid support) | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by TP Diffenbach [ 25/Oct/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
I've created a patch for optionally giving local time (server's local time) for the pipeline date expressions. Due to some limitations on the C standard library, it only supports local time, not arbitrary time zones. E.g.: } } } } } } The date parts returned are compatible with what the Javacript Date non-UTC functions return, and DST is compensated for. (Compatible, not always equal, as the date expressions as currently implemented return MYSQL-equivalent values.) The patch can be found here: | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Jonas [ 14/Sep/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
That is the approach that I decided to take as well (do not forget to compensate for daylight saving time). If you only need to support one timezone that seems like the way to go. If anyone has another idea please share. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Ry Walker [ 14/Sep/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
I've just hit this problem today. I need to get aggregate data out of mongo based on our customer's time zone. My approach: I compute a start time and end time, in UTC, which I can pass to MongoDB. Then, I use $year, $month, and $dayOfMonth in my $project operation, which is later used in a $group operation. The problem: These date operators don't know what time zone I care about, and my data won't be grouped according to my target time zone. If there is no immediate plan for timezone support in Mongo, any advice on how I can use aggregation framework to group data by date according to a local timezone? My best idea would be pre-compute & write attributes in the document for what the day/month/year are according to the customer's time zone, then I could avoid using $year, $month, and $dayOfMonth operators. Does that sound like the best workaround?
| |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Gijs Kunze [ 11/Sep/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
While date arithmetic could certainly help, I don't think it's good enough, you'd have to do conditional arithmetic based on whether a date falls in daylight savings time (assuming the current timezone uses dst) which could become very complex if you aggregate data over multiple years. So I'd prefer real timezone support. | |||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Joe Davis [ 25/Aug/12 ] | |||||||||||||||||||||||||||||||||||||||||||||||
|
This would be very useful for us as well, we cannot group by date components using a company's timezone (think MSP where we have a platform that stores data for N companies). MySQL, Oracle, etc all have this. See "date_sub" function in mysql: SQL Server "dateAdd": |