I am using Mongo's mapReduce function to aggregate meter generation
data for a specific home within a specified date range. The problem
is that I cannot get Mongo to query the date ranges based on UTC, it
always seems to be apply my local timezone offset (EST -5 hours) to
the range no matter what I do.
After I run the function my resulting output is not really what I
expect it to be. It "inaccurately" bundled the generation data from
3-1 into 2-28.. hmm..
In an attempt to fix this I tried forcing the initial date range from
JUnit to be in UTC like so:
final Interval range = new Interval(new
DateTime(2011,3,1,0,0,0,0).withZone(DateTimeZone.UTC), new
DateTime(2011,3,2,23,59,59,999).withZone(DateTimeZone.UTC));
Cool, so now the map reduce function is in plain UTC and doesn't
consider my local timezone offset:
I have been struggling heavily with these date range queries and am
pretty much running out of things to try. Why is it that no matter
what I do Mongo always just uses my local timezone when looking at the
data?
In short, the group in this discussion determines that it's best for everything in the application layer to be based on UTC and for local timezones to only be relevant when presenting your data. I definitely agree and have been trying to adjust my app to fit this. However, Mongo does not seem to be enforcing this concept. Despite forcing the use of UTC just about everywhere I can think of, I still am not receiving the expected results.
However, I was able to get the desired results by first modifying my initial raw data in Mongo to account for my local timezone offset (EST, -5 hours). I simply added 5 hours to my original raw data (00:00 became 05:00, 01:00 became 06:00, etc):
Clearly Mongo always applies my local timezone to the date range query, this is why making the raw data consider my local timezone gives me the correct output. Given this, I tried forcing my local timezone in Java's Date class and Joda DateTime to be UTC:
I also modified my raw data in Mongo to no longer consider my local timezone, so 00:00 is represented as 00:00 instead of as 05:00. My resulting query was:
Unfortunately, I did not receive the results I need although I feel like I should have. The generation data is mostly correc, but the dates are off. 2011-3-1 should have the generation for 2011-2-28 (25.) and 2011-3-2 should combine the generation from 2011-3-1 and 2011-3-2 to make 60. The skewed date results occur because Mongo is still applying my local timezone at some level.
Because forcing UTC in Joda DateTime and Java's Date class has provided no benefit, I'm suspecting interference from Mongo's JavaScript engine. Is there a configuration property in Mongo where I can force UTC to be used in my queries? I cannot find anything on this in the Mongo documentation or anywhere else really.
It looks like this is a bug in the implementation of the getDate()
method of the JavaScript date Object. It seems that that method is
doing timezone conversion to the local time of the server (or shell,
when running in that context). I've opened https://jira.mongodb.org/browse/SERVER-5774 to track the issue.
- Dan
On May 6, 1:21 pm, Erik Vavro <eva...@gmail.com> wrote:
> In short, the group in this discussion determines that it's best for
> everything in the application layer to be based on UTC and for local
> timezones to only be relevant when presenting your data. I definitely
> agree and have been trying to adjust my app to fit this. However, Mongo
> does not seem to be enforcing this concept. Despite forcing the use of UTC
> just about everywhere I can think of, I still am not receiving the expected
> results.
> However, I was able to get the desired results by first modifying my
> initial raw data in Mongo to account for my local timezone offset (EST, -5
> hours). I simply added 5 hours to my original raw data (00:00 became
> 05:00, 01:00 became 06:00, etc):
> Clearly Mongo always applies my local timezone to the date range query,
> this is why making the raw data consider my local timezone gives me the
> correct output. Given this, I tried forcing my local timezone in Java's
> Date class and Joda DateTime to be UTC:
> I also modified my raw data in Mongo to no longer consider my local
> timezone, so 00:00 is represented as 00:00 instead of as 05:00. My
> resulting query was:
> Unfortunately, I did not receive the results I need although I feel like I
> should have. The generation data is mostly correc, but the dates are off.
> 2011-3-1 should have the generation for 2011-2-28 (25.) and 2011-3-2
> should combine the generation from 2011-3-1 and 2011-3-2 to make 60. The
> skewed date results occur because Mongo is still applying my local timezone
> at some level.
> Because forcing UTC in Joda DateTime and Java's Date class has provided no
> benefit, I'm suspecting interference from Mongo's JavaScript engine. Is
> there a configuration property in Mongo where I can force UTC to be used in
> my queries? I cannot find anything on this in the Mongo documentation or
> anywhere else really.
Ah, I've just been informed that JavaScript Dates have a getUTCDate()
method (and other getUTC* methods) which you should use for server-
side javascript (like MapReduce). This, combined with your earlier
approach of explicitly converting Joda Time dates to UTC should work
and do what you expect.
- Dan
On May 7, 12:56 pm, Dan Crosta <dcro...@10gen.com> wrote:
> It looks like this is a bug in the implementation of the getDate()
> method of the JavaScript date Object. It seems that that method is
> doing timezone conversion to the local time of the server (or shell,
> when running in that context). I've openedhttps://jira.mongodb.org/browse/SERVER-5774 > to track the issue.
> - Dan
> On May 6, 1:21 pm, Erik Vavro <eva...@gmail.com> wrote:
> > Okay, I have obtained some insight as to what's going on. I found some
> > useful information here:
> > In short, the group in this discussion determines that it's best for
> > everything in the application layer to be based on UTC and for local
> > timezones to only be relevant when presenting your data. I definitely
> > agree and have been trying to adjust my app to fit this. However, Mongo
> > does not seem to be enforcing this concept. Despite forcing the use of UTC
> > just about everywhere I can think of, I still am not receiving the expected
> > results.
> > However, I was able to get the desired results by first modifying my
> > initial raw data in Mongo to account for my local timezone offset (EST, -5
> > hours). I simply added 5 hours to my original raw data (00:00 became
> > 05:00, 01:00 became 06:00, etc):
> > Clearly Mongo always applies my local timezone to the date range query,
> > this is why making the raw data consider my local timezone gives me the
> > correct output. Given this, I tried forcing my local timezone in Java's
> > Date class and Joda DateTime to be UTC:
> > I also modified my raw data in Mongo to no longer consider my local
> > timezone, so 00:00 is represented as 00:00 instead of as 05:00. My
> > resulting query was:
> > Unfortunately, I did not receive the results I need although I feel like I
> > should have. The generation data is mostly correc, but the dates are off.
> > 2011-3-1 should have the generation for 2011-2-28 (25.) and 2011-3-2
> > should combine the generation from 2011-3-1 and 2011-3-2 to make 60. The
> > skewed date results occur because Mongo is still applying my local timezone
> > at some level.
> > Because forcing UTC in Joda DateTime and Java's Date class has provided no
> > benefit, I'm suspecting interference from Mongo's JavaScript engine. Is
> > there a configuration property in Mongo where I can force UTC to be used in
> > my queries? I cannot find anything on this in the Mongo documentation or
> > anywhere else really.
Thank you so much for your help, I was finally able to achieve my expected results using the information you gave me. The skewed results were coming from my mapping function at the hourly generation level (one level of abstraction behind my daily generation mapping function that was mentioned in my original post). I made the following change to my mapping functions:
In my test cases (using JUnit) I also added the following to enforce UTC in Joda DateTime and Java Date on my machine:
@Before public void setUp() { TimeZone.setDefault(TimeZone.getTimeZone("UTC")); DateTimeZone.setDefault(DateTimeZone.UTC);
}
Simply enforcing the timezone in your java classes is not sufficient because Mongo will apply your local timezone at the JavaScript level before it performs the map reduction. The key is that, in addition to the global timezone changes, you need to explicitly define UTC using the getUTC... methods in your mapping function(s).
I am now able to store my data in Mongo in UTC instead of having to manually apply my -5 hour offset.
On Monday, May 7, 2012 1:12:26 PM UTC-4, Dan Crosta wrote:
> Ah, I've just been informed that JavaScript Dates have a getUTCDate() > method (and other getUTC* methods) which you should use for server- > side javascript (like MapReduce). This, combined with your earlier > approach of explicitly converting Joda Time dates to UTC should work > and do what you expect.
> - Dan
> On May 7, 12:56 pm, Dan Crosta <dcro...@10gen.com> wrote: > > It looks like this is a bug in the implementation of the getDate() > > method of the JavaScript date Object. It seems that that method is > > doing timezone conversion to the local time of the server (or shell, > > when running in that context). I've openedhttps:// > jira.mongodb.org/browse/SERVER-5774 > > to track the issue.
> > - Dan
> > On May 6, 1:21 pm, Erik Vavro <eva...@gmail.com> wrote:
> > > Okay, I have obtained some insight as to what's going on. I found > some > > > useful information here:
> > > In short, the group in this discussion determines that it's best for > > > everything in the application layer to be based on UTC and for local > > > timezones to only be relevant when presenting your data. I definitely > > > agree and have been trying to adjust my app to fit this. However, > Mongo > > > does not seem to be enforcing this concept. Despite forcing the use of > UTC > > > just about everywhere I can think of, I still am not receiving the > expected > > > results.
> > > However, I was able to get the desired results by first modifying my > > > initial raw data in Mongo to account for my local timezone offset > (EST, -5 > > > hours). I simply added 5 hours to my original raw data (00:00 became > > > 05:00, 01:00 became 06:00, etc):
> > > final Interval range = new Interval(new > DateTime(2011,3,1,0,0,0,0), new > > > DateTime(2011,3,1,23,59,59,999));
> > > I constructed my query using BasicDBObject (exactly as I did in my > previous > > > post) and the resulting query used by my map reduce function is:
> > > Clearly Mongo always applies my local timezone to the date range > query, > > > this is why making the raw data consider my local timezone gives me > the > > > correct output. Given this, I tried forcing my local timezone in > Java's > > > Date class and Joda DateTime to be UTC:
> > > I also modified my raw data in Mongo to no longer consider my local > > > timezone, so 00:00 is represented as 00:00 instead of as 05:00. My > > > resulting query was:
> > > Unfortunately, I did not receive the results I need although I feel > like I > > > should have. The generation data is mostly correc, but the dates are > off. > > > 2011-3-1 should have the generation for 2011-2-28 (25.) and 2011-3-2 > > > should combine the generation from 2011-3-1 and 2011-3-2 to make 60. > The > > > skewed date results occur because Mongo is still applying my local > timezone > > > at some level.
> > > Because forcing UTC in Joda DateTime and Java's Date class has > provided no > > > benefit, I'm suspecting interference from Mongo's JavaScript engine. > Is > > > there a configuration property in Mongo where I can force UTC to be > used in > > > my queries? I cannot find anything on this in the Mongo > documentation or > > > anywhere else really.
On Tue, May 8, 2012 at 9:15 AM, evavro <eva...@gmail.com> wrote:
> Dan,
> Thank you so much for your help, I was finally able to achieve my expected
> results using the information you gave me. The skewed results were coming
> from my mapping function at the hourly generation level (one level of
> abstraction behind my daily generation mapping function that was mentioned
> in my original post). I made the following change to my mapping functions:
> Simply enforcing the timezone in your java classes is not sufficient because
> Mongo will apply your local timezone at the JavaScript level before it
> performs the map reduction. The key is that, in addition to the global
> timezone changes, you need to explicitly define UTC using the getUTC...
> methods in your mapping function(s).
> I am now able to store my data in Mongo in UTC instead of having to manually
> apply my -5 hour offset.
> Hope this helps someone along the way :)
> On Monday, May 7, 2012 1:12:26 PM UTC-4, Dan Crosta wrote:
>> Ah, I've just been informed that JavaScript Dates have a getUTCDate()
>> method (and other getUTC* methods) which you should use for server-
>> side javascript (like MapReduce). This, combined with your earlier
>> approach of explicitly converting Joda Time dates to UTC should work
>> and do what you expect.
>> - Dan
>> On May 7, 12:56 pm, Dan Crosta <dcro...@10gen.com> wrote:
>> > It looks like this is a bug in the implementation of the getDate()
>> > method of the JavaScript date Object. It seems that that method is
>> > doing timezone conversion to the local time of the server (or shell,
>> > when running in that context). I've
>> > openedhttps://jira.mongodb.org/browse/SERVER-5774 >> > to track the issue.
>> > - Dan
>> > On May 6, 1:21 pm, Erik Vavro <eva...@gmail.com> wrote:
>> > > Okay, I have obtained some insight as to what's going on. I found
>> > > some
>> > > useful information here:
>> > > In short, the group in this discussion determines that it's best for
>> > > everything in the application layer to be based on UTC and for local
>> > > timezones to only be relevant when presenting your data. I definitely
>> > > agree and have been trying to adjust my app to fit this. However,
>> > > Mongo
>> > > does not seem to be enforcing this concept. Despite forcing the use of
>> > > UTC
>> > > just about everywhere I can think of, I still am not receiving the
>> > > expected
>> > > results.
>> > > However, I was able to get the desired results by first modifying my
>> > > initial raw data in Mongo to account for my local timezone offset
>> > > (EST, -5
>> > > hours). I simply added 5 hours to my original raw data (00:00 became
>> > > 05:00, 01:00 became 06:00, etc):
>> > > final Interval range = new Interval(new
>> > > DateTime(2011,3,1,0,0,0,0), new
>> > > DateTime(2011,3,1,23,59,59,999));
>> > > I constructed my query using BasicDBObject (exactly as I did in my
>> > > previous
>> > > post) and the resulting query used by my map reduce function is:
>> > > Clearly Mongo always applies my local timezone to the date range
>> > > query,
>> > > this is why making the raw data consider my local timezone gives me
>> > > the
>> > > correct output. Given this, I tried forcing my local timezone in
>> > > Java's
>> > > Date class and Joda DateTime to be UTC:
>> > > I also modified my raw data in Mongo to no longer consider my local
>> > > timezone, so 00:00 is represented as 00:00 instead of as 05:00. My
>> > > resulting query was:
>> > > Unfortunately, I did not receive the results I need although I feel
>> > > like I
>> > > should have. The generation data is mostly correc, but the dates are
>> > > off.
>> > > 2011-3-1 should have the generation for 2011-2-28 (25.) and 2011-3-2
>> > > should combine the generation from 2011-3-1 and 2011-3-2 to make 60.
>> > > The
>> > > skewed date results occur because Mongo is still applying my local
>> > > timezone
>> > > at some level.
>> > > Because forcing UTC in Joda DateTime and Java's Date class has
>> > > provided no
>> > > benefit, I'm suspecting interference from Mongo's JavaScript engine.
>> > > Is
>> > > there a configuration property in Mongo where I can force UTC to be
>> > > used in
>> > > my queries? I cannot find anything on this in the Mongo
>> > > documentation or
>> > > anywhere else really.
> To post to this group, send email to mongodb-user@googlegroups.com.
> To unsubscribe from this group, send email to
> mongodb-user+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/mongodb-user?hl=en.