# Date histogram & time zones **URL:** \ **Category:** Elasticsearch\ **Created:** [February 21, 2012, 7:07am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764 "2012-02-21T07:07:57Z")\ **Posts on this page:** 19\ **Page:** 1
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 21, 2012, 7:07am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/1 "2012-02-21T07:07:57Z")
I created a facet like so: FacetBuilders.dateHistogramFacet("date") .field("timestamp").interval("month").zone("-08:00"); And extracted the results like so: DateHistogramFacet date = facets.facet(DateHistogramFacet.class, id); for (DateHistogramFacet.Entry entry : date.entries()) { long time = entry.getTime(); ... } The first time value was 1296518400000, which is 2011-02-01T00:00:00.000Z or 2011-01-31T16:00:00.000-08:00. Shouldn't this have been 2011-02-01T00:00:00.000-08:00? ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 21, 2012, 10:43am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/2 "2012-02-21T10:43:49Z")
Which version are you using? On Tuesday, February 21, 2012 at 9:07 AM, Eric Jain wrote: > I created a facet like so: > > FacetBuilders.dateHistogramFacet("date") > .field("timestamp").interval("month").zone("-08:00"); > > And extracted the results like so: > > DateHistogramFacet date = facets.facet(DateHistogramFacet.class, > id); > for (DateHistogramFacet.Entry entry : date.entries()) { > long time = entry.getTime(); > ... > } > > The first time value was 1296518400000, which is > 2011-02-01T00:00:00.000Z or 2011-01-31T16:00:00.000-08:00. Shouldn't > this have been 2011-02-01T00:00:00.000-08:00? ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 21, 2012, 7:52pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/3 "2012-02-21T19:52:28Z")
On Tue, Feb 21, 2012 at 02:43, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > Which version are you using? I was using 0.19.0.RC2. But I see the same behavior after upgrading to 0.19.0.RC3 (and replacing the call to .zone with .preZone). ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 21, 2012, 11:43pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/4 "2012-02-21T23:43:39Z")
Yes, preZone will apply the zone offset before rounding, but still return in UTC, you can add postZone to apply it post rounding as well. (this is new in 0.19, and helps fix a problem where zone rounding did not work for lower than day resolution, as well as enhance the date histogram to still return UTC dates - where you sometimes need it, mainly to trick javascript char libs 🙂 ). On Tuesday, February 21, 2012 at 9:52 PM, Eric Jain wrote: > On Tue, Feb 21, 2012 at 02:43, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > Which version are you using? > > I was using 0.19.0.RC2. But I see the same behavior after upgrading to > 0.19.0.RC3 (and replacing the call to .zone with .preZone). ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 22, 2012, 12:57am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/5 "2012-02-22T00:57:55Z")
On Tue, Feb 21, 2012 at 15:43, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > Yes, preZone will apply the zone offset before rounding, but still return in > UTC, you can add postZone to apply it post rounding as well. (this is new in > 0.19, and helps fix a problem where zone rounding did not work for lower > than day resolution, as well as enhance the date histogram to still return > UTC dates - where you sometimes need it, mainly to trick javascript char > libs 🙂 ). So I do .preZone("-08:00").postZone("-08:00"), but DateHistogramFacet.Entry.getTime() still returns the millis truncated to the month using UTC. btw is .preZone("-8") supposed to work? It's mentioned at [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/date-histogram-facet.html), but is rejected , at least when using the Java API. ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 24, 2012, 1:25pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/6 "2012-02-24T13:25:12Z")
What you will get with postZone is the start of the month in UTC offseted by the time zone. If you don't see it, then create a test that shows it (Here is the test that is used in ES: [https://github.com/elasticsearch/elasticsearch/blob/master/src/test/java/org/elasticsearch/test/integration/search/facet/SimpleFacetsTests.java#L1405](https://github.com/elasticsearch/elasticsearch/blob/master/src/test/java/org/elasticsearch/test/integration/search/facet/SimpleFacetsTests.java#L1405)). And yea, in json, the numeric value -2 is supported, but in Java, its a string value, so it always needs to be in time zone offset format (which is recommended). On Wednesday, February 22, 2012 at 2:57 AM, Eric Jain wrote: > On Tue, Feb 21, 2012 at 15:43, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > Yes, preZone will apply the zone offset before rounding, but still return in > > UTC, you can add postZone to apply it post rounding as well. (this is new in > > 0.19, and helps fix a problem where zone rounding did not work for lower > > than day resolution, as well as enhance the date histogram to still return > > UTC dates - where you sometimes need it, mainly to trick javascript char > > libs 🙂 ). > > So I do .preZone("-08:00").postZone("-08:00"), but > DateHistogramFacet.Entry.getTime() still returns the millis truncated > to the month using UTC. > > btw is .preZone("-8") supposed to work? It's mentioned at > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/date-histogram-facet.html), > but is rejected , at least when using the Java API. ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 24, 2012, 6:25pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/7 "2012-02-24T18:25:39Z")
On Fri, Feb 24, 2012 at 05:25, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > What you will get with postZone is the start of the month in UTC offseted by > the time zone. I expected the start of the month to be given using the specified time zone rather than UTC, but it's not a big deal as the counts appear to be correct. ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 26, 2012, 7:34pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/8 "2012-02-26T19:34:07Z")
The start time will be expected to be UTC if no time zone information is provided in the date string. The date format already supports providing the relevant time zone. On Friday, February 24, 2012 at 8:25 PM, Eric Jain wrote: > On Fri, Feb 24, 2012 at 05:25, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > What you will get with postZone is the start of the month in UTC offseted by > > the time zone. > > I expected the start of the month to be given using the specified time > zone rather than UTC, but it's not a big deal as the counts appear to > be correct. ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 27, 2012, 6:28pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/9 "2012-02-27T18:28:59Z")
On Sun, Feb 26, 2012 at 11:34, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > What you will get with postZone is the start of the month in UTC offseted by > the time zone. If I do: "date\_histogram" : { "field" : "timestamp", "interval" : "month", "pre\_zone" : "-08:00", "post\_zone" : "-08:00" } I get 1296489600000L, which is 2011-01-31T08:00:00.000-08:00 or 2011-01-31T16:00:00.000Z. To get 2011-02, I can either not set post\_zone and work around the fact the the millis returned represent 2011-02-01T00:00:00.000Z instead of 2011-02-01T00:00:00.000-08:00, or else set post\_zone to +08:00. This looks like a bug to me--unless I'm still misunderstanding how this is supposed to work... ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 27, 2012, 8:12pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/10 "2012-02-27T20:12:26Z")
I think you have a problem in how you construct the date and test for it. The one without the preOffset will return the mills in UTC, and the one with the postOffset will return the time in and offset against the UTC. Here is a simple test case: [gist:1926706 · GitHub](https://gist.github.com/1926706). On Monday, February 27, 2012 at 8:28 PM, Eric Jain wrote: > On Sun, Feb 26, 2012 at 11:34, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > What you will get with postZone is the start of the month in UTC offseted by > > the time zone. > > If I do: > > "date\_histogram" : { > "field" : "timestamp", > "interval" : "month", > "pre\_zone" : "-08:00", > "post\_zone" : "-08:00" > } > > I get 1296489600000L, which is 2011-01-31T08:00:00.000-08:00 or > 2011-01-31T16:00:00.000Z. > > To get 2011-02, I can either not set post\_zone and work around the > fact the the millis returned represent 2011-02-01T00:00:00.000Z > instead of 2011-02-01T00:00:00.000-08:00, or else set post\_zone to > +08:00. > > This looks like a bug to me--unless I'm still misunderstanding how > this is supposed to work... ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 27, 2012, 9:39pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/11 "2012-02-27T21:39:41Z")
On Mon, Feb 27, 2012 at 12:12, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > I think you have a problem in how you construct the date and test for it. > The one without the preOffset will return the mills in UTC, and the one with > the postOffset will return the time in and offset against the UTC. Here is a > simple test case: [gist:1926706 · GitHub](https://gist.github.com/1926706). I'm expecting 'new DateTime(facet2.getEntries().get(0).time(), DateTimeZone.forOffsetHours(-8))' to be '2012-01-01T00:00:00.000-08:00' ('utcExpected - TimeUnit.HOURS.toMillis(-8)'), not '2011-12-31T08:00:00.000-08:00' ('utcExpected - TimeUnit.HOURS.toMillis(8)'). ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 28, 2012, 11:02am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/12 "2012-02-28T11:02:52Z")
You are not using Joda correctly, check the javadoc for DateTime(long instant, DateTimeZone zone). The instant is expected to be UTC milliseconds. What you end up doing is getting back from elasticsearch the time already with the time zone (-8), and then "apply" it again when creating the DateTime (another -8). On Monday, February 27, 2012 at 11:39 PM, Eric Jain wrote: > On Mon, Feb 27, 2012 at 12:12, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > I think you have a problem in how you construct the date and test for it. > > The one without the preOffset will return the mills in UTC, and the one with > > the postOffset will return the time in and offset against the UTC. Here is a > > simple test case: [gist:1926706 · GitHub](https://gist.github.com/1926706). > > I'm expecting 'new DateTime(facet2.getEntries().get(0).time(), > DateTimeZone.forOffsetHours(-8))' to be > '2012-01-01T00:00:00.000-08:00' ('utcExpected - > TimeUnit.HOURS.toMillis(-8)'), not > '2011-12-31T08:00:00.000-08:00' ('utcExpected - TimeUnit.HOURS.toMillis(8)'). ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 28, 2012, 9:55pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/13 "2012-02-28T21:55:45Z")
On Tue, Feb 28, 2012 at 03:02, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > You are not using Joda correctly, check the javadoc for DateTime(long > instant, DateTimeZone zone). The instant is expected to be UTC milliseconds. > What you end up doing is getting back from elasticsearch the time already > with the time zone (-8), and then "apply" it again when creating the > DateTime (another -8). I expected elasticsearch to always return UTC milliseconds, so that explains part of the confusion. But even without postZone, it's not clear to me why I need to add the timezone offset back to the result to get the expected local time for year/month/day intervals, but not for hour/minute intervals. See [https://gist.github.com/1934291](https://gist.github.com/1934291). ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 29, 2012, 12:13am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/14 "2012-02-29T00:13:20Z")
First, your gist does not use postZone, but preZone. Let me explain the work that was done in 0.19 to improve the rounding with time zones (its tricky). Lets say you want to round "2012-04-01T04:15:30Z" (which is in UTC what you use in your test: "2012-03-31T20:15:30-08:00"). First, lets do day level rounding with preZone set to -8. "2012-04-01T04:15:30Z" - 8 falls into 2012-03-31, and it will be returned (since postZone defaults to UTC) in UTC, so it all be "2012-03-31T00:00:00Z". So, the result is rounded and returned in UTC (start of month in UTC). Second, we do hour based rounding with preZone set to -8. "2012-04-01T04:15:30Z" - 8 falls at "2012-03-31T20:15:30", rounding it will mean its "2012-03-31T20:00:00", but, we want to return it in UTC, and the correct rounded time in UTC is: "2012-04-01T04:00:00Z". Here, we do add back the offset, so you get the proper rounded value in UTC. So, you can just do preZone, and create a new DateTime with the returned time (with UTC time zone). It will be the UTC rounded value that you want. If you want to apply a postZone, then you can, and then you will get the mentioned results, just with the offset applied. So, for day you will get back "2012-03-31T00:00:00-08:00", and for hour you will get back: "2012-04-01T04:00:00-08:00". I can see where the confusion is coming from, I can add a flag that would make elasticsearch behave to what you expect in the test, though the above behavior is probably what you want. I will add this flag just so we cover all the options of rounding. On Tuesday, February 28, 2012 at 11:55 PM, Eric Jain wrote: > On Tue, Feb 28, 2012 at 03:02, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > You are not using Joda correctly, check the javadoc for DateTime(long > > instant, DateTimeZone zone). The instant is expected to be UTC milliseconds. > > What you end up doing is getting back from elasticsearch the time already > > with the time zone (-8), and then "apply" it again when creating the > > DateTime (another -8). > > I expected elasticsearch to always return UTC milliseconds, so that > explains part of the confusion. But even without postZone, it's not > clear to me why I need to add the timezone offset back to the result > to get the expected local time for year/month/day intervals, but not > for hour/minute intervals. See [https://gist.github.com/1934291](https://gist.github.com/1934291). ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 29, 2012, 12:59am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/15 "2012-02-29T00:59:17Z")
On Tue, Feb 28, 2012 at 16:13, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > I can see where the confusion is coming from, I can add a flag that would > make elasticsearch behave to what you expect in the test, though the above > behavior is probably what you want. I will add this flag just so we cover > all the options of rounding. Thanks for the detailed explanation! So if I index 2012-03-31T20:15:30-08:00 (or 2012-04-01T04:15:30Z), the new flag would let me get: 2012-01-01T00:00:00-08:00 [interval=year, zone=-08:00] 2012-03-01T00:00:00-08:00 [interval=month, zone=-08:00] 2012-03-31T00:00:00-08:00 [interval=day, zone=-08:00] 2012-03-31T20:00:00-08:00 [interval=hour, zone=-08:00] 2012-03-31T20:15:00-08:00 [interval=minute, zone=-08:00] Correct? ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 29, 2012, 1:26pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/16 "2012-02-29T13:26:25Z")
Yes. Though I still think that its not what you would want, I guess you will get to it when you try and graph it. On Wednesday, February 29, 2012 at 2:59 AM, Eric Jain wrote: > On Tue, Feb 28, 2012 at 16:13, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > I can see where the confusion is coming from, I can add a flag that would > > make elasticsearch behave to what you expect in the test, though the above > > behavior is probably what you want. I will add this flag just so we cover > > all the options of rounding. > > Thanks for the detailed explanation! > > So if I index 2012-03-31T20:15:30-08:00 (or 2012-04-01T04:15:30Z), the > new flag would let me get: > > 2012-01-01T00:00:00-08:00 [interval=year, zone=-08:00] > 2012-03-01T00:00:00-08:00 [interval=month, zone=-08:00] > 2012-03-31T00:00:00-08:00 [interval=day, zone=-08:00] > 2012-03-31T20:00:00-08:00 [interval=hour, zone=-08:00] > 2012-03-31T20:15:00-08:00 [interval=minute, zone=-08:00] > > Correct? ---
**Author:** ![Eric\_Jain](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eric_jain/32/834_2.png) [@Eric\_Jain](https://discuss.elastic.co/u/Eric_Jain)\ **Post date:** [February 29, 2012, 6:31pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/17 "2012-02-29T18:31:13Z")
On Wed, Feb 29, 2012 at 05:26, Shay Banon [kimchy@gmail.com](mailto:kimchy@gmail.com) wrote: > Yes. Though I still think that its not what you would want, I guess you will > get to it when you try and graph it. Perhaps I'm being stupid, but I don't see why I'd ever want 2012-03-31T20:15:30-08:00 (or 2012-04-01T04:15:30Z) rounded to the hour to be 2012-04-01T04:00:00-08:00 rather than 2012-03-31T20:00:00-08:00. Is there an existing ticket, or shall I create one? ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [February 29, 2012, 8:16pm UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/18 "2012-02-29T20:16:52Z")
Its already there: [Date Histogram Facet: Add `pre_zone_adjust_large_interval` · Issue #1744 · elastic/elasticsearch · GitHub](https://github.com/elasticsearch/elasticsearch/issues/1744). On Wednesday, February 29, 2012 at 8:31 PM, Eric Jain wrote: > On Wed, Feb 29, 2012 at 05:26, Shay Banon \<[kimchy@gmail.com](mailto:kimchy@gmail.com) ([mailto:kimchy@gmail.com](mailto:kimchy@gmail.com))\> wrote: > > > Yes. Though I still think that its not what you would want, I guess you will > > get to it when you try and graph it. > > Perhaps I'm being stupid, but I don't see why I'd ever want > 2012-03-31T20:15:30-08:00 (or 2012-04-01T04:15:30Z) rounded to the > hour to be 2012-04-01T04:00:00-08:00 rather than > 2012-03-31T20:00:00-08:00. > > Is there an existing ticket, or shall I create one? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:37am UTC](https://discuss.elastic.co/t/date-histogram-time-zones/6764/19 "2017-07-06T03:37:47Z")