Aggregation based on hour of day and timezone

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!

I found some useful information at Is it possible to apply a timezone to a date in a scripted field? but it seems this approach is not best practice and has been deprecated.

I then stumbled upon https://stackoverflow.com/a/46830875 and was able to get it working with a small tweak :slight_smile:

Just not sure how well this will scale.

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