Aggregation timezones

[Gijs]

I've been evaluating the new aggregation framework in MongoDB 2.1 with the goal of replacing some of our MapReduce jobs with simpler and faster aggregation queries, however I've run into one issue concerning the date operators (http://docs.mongodb.org/manual/reference/aggregation/#date-operators).

We need to group our data by day but since the start and end of the day differs by timezone we currently only seem to be able to group by UTC days. Our application needs to support multiple timezones, so is there anyway to specify an alternate timezone per query?

[craiggwilson]

I'm a little confused by what you are asking.  Do you need to be able to aggregate up all data for, say 2:00PM to 3:00PM, regardless of where in the world that 2:00 to 3:00 existed?

[Gijs]

I'm sorry for the confusion, I need to group data by day, but depending on the timezone of the client requesting the data a day may start at a different timestamp.

So for example, say I have a data point on the 1st of july 11:00:00PM GMT, if a Japanese client would request an aggregation of the data, it should aggregate that data point in the July 2nd 'bucket' since in japan that would be about 9 hours later at 8 am the next day, while someone from the New York would get it in the July 1st bucket since it'd be 7 pm over there.

Essentially, the timezone is part of the request so I can't just set the timezone of the mongodb server assuming that'd work (besides all the yucky global state issues that you'd get from that)

[craiggwilson]

Got it.  You are going to need to calculate the timezone offset before sending the query to mongodb.  So, if a Japanese client request the 1st of July at 11PM, you'll need to convert that to UTC time before sending the request to mongodb.  Upon receiving the results, you'll reapply the offset for display.

[Gijs]

I think you're still misunderstanding, a client doesn't request data for a single date, in that case I could simply add a filter with a $gte and $lt on the date field, what I'm concerned with is a date range, say the entire month of july, or the year 2011 or and range between 2 discrete dates.

My problem lies with the new aggregation framework and specifically the $group pipeline operator's $dayOfMonth operator which doesn't seem to have any timezone functionality built in and thus mis-groups data points that are on different dates in different timezones.

[craiggwilson]

Ok, so I understand your problem.  You can file a SERVER ticket at jira.mongodb.org if you feel this is important.

However, I urge you to consider the business implications of this.  If a manager in Japan gets the "July" report and a manager in America gets the "July" report, these are going to look different even though they queried for exactly the same thing.  That seems a little odd to me and could be very unexpected to users.  Perhaps not, but that is my experience.

[Gijs]

Done, https://jira.mongodb.org/browse/SERVER-6310

As far as business implications, in our case the timezone is set account wide so the same data is generally only viewable from one perspective, and the timezone used is displayed in all reports. They can of course change the time zone in their account settings so I can't pre-calculate the correct buckets at insert time unless I do so for all possible timezones which would increase storage per data point way too much.

However I'd consider it more incorrect and unexpected for people to get data from the future which is quite likely for people west of the prime meridian or from the past for people on the other side of the world.