Account Options

  1. Sign in
The old Google Groups will be going away soon, but your browser is incompatible with the new version.
Google Groups Home
« Groups Home
Local timezone interferences in date-based map reduce query
There are currently too many topics in this group that display first. To make this topic appear first, remove this option from another topic.
There was an error processing your request. Please try again.
flag
  6 messages - Collapse all  -  Translate all to Translated (View all originals)
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful
 
From:
To:
Cc:
Followup To:
Add Cc | Add Followup-to | Edit Subject
Subject:
Validation:
For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon. Listen and type the numbers you hear
 
Erik Vavro  
View profile  
 More options May 4 2012, 3:57 pm
From: Erik Vavro <eva...@gmail.com>
Date: Fri, 4 May 2012 12:57:19 -0700 (PDT)
Local: Fri, May 4 2012 3:57 pm
Subject: Local timezone interferences in date-based map reduce query
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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Erik Vavro  
View profile  
 More options May 6 2012, 1:21 pm
From: Erik Vavro <eva...@gmail.com>
Date: Sun, 6 May 2012 10:21:41 -0700 (PDT)
Local: Sun, May 6 2012 1:21 pm
Subject: Re: Local timezone interferences in date-based map reduce query

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

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.


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Dan Crosta  
View profile  
 More options May 7 2012, 12:56 pm
From: Dan Crosta <dcro...@10gen.com>
Date: Mon, 7 May 2012 09:56:15 -0700 (PDT)
Local: Mon, May 7 2012 12:56 pm
Subject: Re: Local timezone interferences in date-based map reduce query
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:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Dan Crosta  
View profile  
 More options May 7 2012, 1:12 pm
From: Dan Crosta <dcro...@10gen.com>
Date: Mon, 7 May 2012 10:12:26 -0700 (PDT)
Local: Mon, May 7 2012 1:12 pm
Subject: Re: Local timezone interferences in date-based map reduce query
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:


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
evavro  
View profile  
 More options May 8 2012, 12:15 pm
From: evavro <eva...@gmail.com>
Date: Tue, 8 May 2012 09:15:14 -0700 (PDT)
Local: Tues, May 8 2012 12:15 pm
Subject: Re: Local timezone interferences in date-based map reduce query

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


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
Scott Hernandez  
View profile  
 More options May 8 2012, 12:30 pm
From: Scott Hernandez <scotthernan...@gmail.com>
Date: Tue, 8 May 2012 09:30:18 -0700
Local: Tues, May 8 2012 12:30 pm
Subject: Re: [mongodb-user] Re: Local timezone interferences in date-based map reduce query
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...
http://docs.oracle.com/javase/1.4.2/docs/api/java/util/Date.html#getTime()


 
You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.
End of messages
« Back to Discussions « Newer topic     Older topic »