# Aggregation based on hour of day and timezone

**URL:** <https://discuss.elastic.co/t/aggregation-based-on-hour-of-day-and-timezone/110445>\
**Category:** Elasticsearch\
**Created:** [December 6, 2017, 2:02am UTC](https://discuss.elastic.co/t/aggregation-based-on-hour-of-day-and-timezone/110445 "2017-12-06T02:02:34Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![beng](https://avatars.discourse-cdn.com/v4/letter/b/b3f665/32.png) [@beng](https://discuss.elastic.co/u/beng)\
**Post date:** [December 6, 2017, 2:02am UTC](https://discuss.elastic.co/t/aggregation-based-on-hour-of-day-and-timezone/110445/1 "2017-12-06T02:02:35Z")

</div>

We currently have a stat known as "Peak Hours" and this shows total counts based on a field called "created\_at\_hour" (this field is stored as the UTC hour). We did it this way to avoid using a script to calculate the hour based on a timestamp etc.

The issue we're running into is if something is created at 6PM CST, it's stored as hour 0 in UTC. When we aggregate this data, it will come back as 0 instead of 6 (obviously because it’s stored as 0 and is an integer)

Is there a better way to do an aggregation like this as of Elastic 5.6.3 based off of a created\_at timestamp? Everything online pointed to using ES side scripts but that doesn’t seem to scale well.

Thanks in advance!

---

<div class="post-metadata">

**Author:** ![beng](https://avatars.discourse-cdn.com/v4/letter/b/b3f665/32.png) [@beng](https://discuss.elastic.co/u/beng)\
**Post date:** [December 6, 2017, 2:21pm UTC](https://discuss.elastic.co/t/aggregation-based-on-hour-of-day-and-timezone/110445/2 "2017-12-06T14:21:51Z")

</div>

I found some useful information at [Is it possible to apply a timezone to a date in a scripted field?](https://discuss.elastic.co/t/is-it-possible-to-apply-a-timezone-to-a-date-in-a-scripted-field/65372) but it seems this approach is not best practice and has been deprecated.

I then stumbled upon [https://stackoverflow.com/a/46830875](https://stackoverflow.com/a/46830875) and was able to get it working with a small tweak 🙂

Just not sure how well this will scale.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 3, 2018, 2:31pm UTC](https://discuss.elastic.co/t/aggregation-based-on-hour-of-day-and-timezone/110445/3 "2018-01-03T14:31:42Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.