Local timezone interferences in date-based map reduce query

[Erik Vavro]

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.

Here is my raw data in Mongo:

{ "_id" : NumberLong(180240), "className" :
"models.generation.MeterMessage", "gin" : "1.1", "sn" : "567890",
"at" : ISODate("2011-03-01T00:00:00Z"), "gen" : 10,
"aggregationStatus" : false }
{ "_id" : NumberLong(180241), "className" :
"models.generation.MeterMessage", "gin" : "1.1", "sn" : "567890",
"at" : ISODate("2011-03-01T01:00:00Z"), "gen" : 10,
"aggregationStatus" : false }
{ "_id" : NumberLong(180242), "className" :
"models.generation.MeterMessage", "gin" : "1.1", "sn" : "567890",
"at" : ISODate("2011-03-01T02:00:00Z"), "gen" : 5,
"aggregationStatus" : false }
{ "_id" : NumberLong(180243), "className" :
"models.generation.MeterMessage", "gin" : "1.1", "sn" : "567890",
"at" : ISODate("2011-03-02T01:00:00Z"), "gen" : 20,
"aggregationStatus" : true }
{ "_id" : NumberLong(180244), "className" :
"models.generation.MeterMessage", "gin" : "1.1", "sn" : "567890",
"at" : ISODate("2011-03-02T02:00:00Z"), "gen" : 20,
"aggregationStatus" : true }
{ "_id" : NumberLong(180245), "className" :
"models.generation.MeterMessage", "gin" : "1.1", "sn" : "567890",
"at" : ISODate("2011-03-03T00:00:39Z"), "gen" : 20,
"aggregationStatus" : true }

I am using JUnit for testing, and this is how I am passing in my date
range to the aggregator (using Joda DateTime).

final Interval range = new Interval(new
DateTime(2011,3,1,0,0,0,0), new DateTime(2011,3,2,23,59,59,999));

In my aggregation method I use a BasicDBObject to build part of my
query:

BasicDBObject obj = new BasicDBObject("$gte",
dateRange.getStart().toDate()).append("$lt",
dateRange.getEnd().toDate());

The resulting query used by the map reduce function is:

{ "gin" : "1.1" , "at" : { "$gte" : { "$date" :
"2011-03-01T05:00:00.000Z"} , "$lt" : { "$date" :
"2011-03-03T04:59:59.999Z"}}}

Here are my map / reduce functions and the call I make the Mongo to
perform the map reduce

final String dailyGenerationMapFunction =
"function() { " +
" emit (" +
" this.at.getFullYear() + '-' + (this.at.getMonth()+1) + '-'
+ this.at.getDate(), " +
" { at: this.at, gen: this.gen }); " +
"}";

final String sumGenerationReduceFunction =
"function(v, vals) {" +
" var sum = 0; " +
" for(var i in vals) {" +
" sum += vals[i].gen;" +
" }" +
" return { at: v, gen: sum };"+
"}";

I call Mongo's mapReduce function like so (I doubt it matters but my
collection is obtained through Morphia):

DBCollection dbCol =
MorphiaPlugin.ds().getCollection(MeterMessage.class)

dbCol. mapReduce(mapFunction, reduceFunction, "GenerationOutput",
query)

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

{ "_id" : "2011-2-28", "value" : { "at" : "2011-2-28" , "gen" :
25.0}}
{ "_id" : "2011-3-1", "value" : { "at" : "2011-3-1" , "gen" :
40.0}}
{ "_id" : "2011-3-2", "value" : { "at" : { "$date" :
"2011-03-03T00:00:39.000Z"} , "gen" : 20.0}}

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:

{ "gin" : "1.1" , "at" : { "$gte" : { "$date" :
"2011-03-01T00:00:00.000Z"} , "$lt" : { "$date" :
"2011-03-03T23:59:59.999Z"}}}

However, the resulting aggregated data remains unchanged...:

{ "_id" : "2011-2-28" , "value" : { "at" : "2011-2-28" , "gen" :
25.0}}
{ "_id" : "2011-3-1" , "value" : { "at" : "2011-3-1" , "gen" :
40.0}}
{ "_id" : "2011-3-2" , "value" : { "at" : { "$date" :
"2011-03-03T00:00:39.000Z"} , "gen" : 20.0}}

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?

Thanks

Erik

[Erik Vavro]

Okay, I have obtained some insight as to what's going on.  I found some useful information here:

http://groups.google.com/group/norm-mongodb/browse_thread/thread/8242170a66459aeb

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):

    { "_id" : NumberLong(182299), "className" : "models.generation.parse.smartsynch.IDFMeterMessage", "gin" : "1.1", "sn" : "567890", "at" : ISODate("2011-03-01T05:00:00Z"), "gen" : 10, "aggregationStatus" : true }

    { "_id" : NumberLong(182300), "className" : "models.generation.parse.smartsynch.IDFMeterMessage", "gin" : "1.1", "sn" : "567890", "at" : ISODate("2011-03-01T06:00:00Z"), "gen" : 10, "aggregationStatus" : true }

    { "_id" : NumberLong(182301), "className" : "models.generation.parse.smartsynch.IDFMeterMessage", "gin" : "1.1", "sn" : "567890", "at" : ISODate("2011-03-01T07:00:00Z"), "gen" : 5, "aggregationStatus" : true }

    { "_id" : NumberLong(182302), "className" : "models.generation.parse.smartsynch.IDFMeterMessage", "gin" : "1.1", "sn" : "567890", "at" : ISODate("2011-03-02T06:00:00Z"), "gen" : 20, "aggregationStatus" : false }

    { "_id" : NumberLong(182303), "className" : "models.generation.parse.smartsynch.IDFMeterMessage", "gin" : "1.1", "sn" : "567890", "at" : ISODate("2011-03-02T02:00:00Z"), "gen" : 20, "aggregationStatus" : true }

    { "_id" : NumberLong(182304), "className" : "models.generation.parse.smartsynch.IDFMeterMessage", "gin" : "1.1", "sn" : "567890", "at" : ISODate("2011-03-02T23:00:00Z"), "gen" : 20, "aggregationStatus" : false }

I used the following Joda date range:

    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:

    { "gin" : "1.1" , "at" : { "$gte" : { "$date" : "2011-03-01T00:00:00.000Z"} , "$lt" : { "$date" : "2011-03-03T23:59:59.999Z"}}}

I used the same map / reduce functions described in my previous post and actually received the expected aggregated output:

    { "_id" : "2011-3-1", "value" : { "at" : "2011-3-1", "gen" : 25 } }

    { "_id" : "2011-3-2", "value" : { "at" : "2011-3-2", "gen" : 60 } }

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:

    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));

    DateTimeZone.setDefault(DateTimeZone.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:

   { "gin" : "1.1" , "at" : { "$gte" : { "$date" : "2011-03-01T00:00:00.000Z"} , "$lt" : { "$date" : "2011-03-02T23:59:59.999Z"}}}

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.

   { "_id" : "2011-2-28", "value" : { "at" : "2011-2-28", "gen" : 25 } }

   { "_id" : "2011-3-1", "value" : { "at" : "2011-3-1", "gen" : 40 } }

   { "_id" : "2011-3-2", "value" : { "at" : ISODate("2011-03-02T23:00:00Z"), "gen" : 20 } }

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.

[Dan Crosta]

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:
> Okay, I have obtained some insight as to what's going on.  I found some
> useful information here:
>

> http://groups.google.com/group/norm-mongodb/browse_thread/thread/8242...

> I used the same map / reduce functions described in my previous post and *
> actually* received the expected aggregated output:

[Dan Crosta]

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

[evavro]

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:

final String hourlyGenerationMapFunction = 

"function() { " +

"    emit (" + 

"        this.at.getFullYear() + '-' + (this.at.getUTCMonth()+1) + '-' + this.at.getUTCDate() + ' ' + this.at.getUTCHours() + ':00', " + 

"        { at: Date.UTC(this.at), gen: this.gen}); " +

"}";

final String dailyGenerationMapFunction = 

"function() { " +

"    emit (" + 

"        this.at.getFullYear() + '-' + (this.at.getUTCMonth()+1) + '-' + this.at.getUTCDate(), " + 

"        { at: Date.UTC(this.at), gen: this.gen }); " +

"}";

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.

Hope this helps someone along the way :)

[Scott Hernandez]

In java you do not need to convert/set at all, The values stored on
the server will be converted for you from your local timezone.

https://github.com/mongodb/mongo-java-driver/blob/master/src/main/org/bson/BasicBSONEncoder.java#L351
http://docs.oracle.com/javase/1.4.2/docs/api/java/util/Date.html#getTime()

> --
> You received this message because you are subscribed to the Google Groups
> "mongodb-user" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/mongodb-user/-/ITMZVMx41ZoJ.
>
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to
> mongodb-user...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/mongodb-user?hl=en.