# Date histogram with different Timezones with Elasticsearch v.7.13.3

**URL:** <https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639>\
**Category:** Elasticsearch\
**Created:** [August 6, 2021, 10:11am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639 "2021-08-06T10:11:44Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![scorona85](https://avatars.discourse-cdn.com/v4/letter/s/d07c76/32.png) [@scorona85](https://discuss.elastic.co/u/scorona85)\
**Post date:** [August 6, 2021, 10:11am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/1 "2021-08-06T10:11:44Z")

</div>

I have to acquire datas from different part of Timezones (example New York -6.00 and Rome +2.00). In the document I have a field 'timestamp' defined as "data" and I have for example create a "date\_histogram" for example from 8.00 AM to 9.00 AM. How can I match the USA 8.00-9.00 and the ITA 8.00-9.00 datas in order to compare the two data from the same period?

This is my datas with two different fuse. 2 from USA and 2 from ITA:

```auto
"hits" : [
  {
    "_index" : "test-data-2021-8-4",
    "_type" : "_doc",
    "_id" : "9tS4EHsB4Ke8qtFfYqbg",
    "_score" : 1.0,
    "_source" : {
      "id" : "mtKDIsEfSr3I8AwCDE1Gjw_11",
      "value" : 87.2,
      "timestamp" : "2021-08-04T12:32:04+02:00"
    }
  },
  {
    "_index" : "test-data-2021-8-4",
    "_type" : "_doc",
    "_id" : "99S4EHsB4Ke8qtFfYqbg",
    "_score" : 1.0,
    "_source" : {
      "id" : "mtKDIsEfSr3I8AwCDE1Gjw_5",
      "value" : 31.0025,
      "timestamp" : "2021-08-04T12:32:04+02:00"
    }
  },
  {
    "_index" : "test-data-2021-8-4",
    "_type" : "_doc",
    "_id" : "wdOREHsB4Ke8qtFfuZAf",
    "_score" : 1.0,
    "_source" : {
      "id" : "mtKDIsEfSr3I8AwCDE1Gjw_11",
      "value" : 15.1,
      "timestamp" : "2021-08-04T05:49:50-04:00"
    }
  },
  {
    "_index" : "test-data-2021-8-4",
    "_type" : "_doc",
    "_id" : "wtOREHsB4Ke8qtFfuZAg",
    "_score" : 1.0,
    "_source" : {
      "id" : "mtKDIsEfSr3I8AwCDE1Gjw_5",
      "value" : 27.9457,
      "timestamp" : "2021-08-04T05:49:50-04:00"
    }
  }
]

```

This is my date\_histogram query:

```auto
 GET /test-data-*/_search?size=10000
{
  "aggs": {
    "agg_sum": {
      "date_histogram": {
        "field": "timestamp",
        "fixed_interval": "1h"
      },
      "aggs": {
        "aggregazione": {
          "sum": {
            "field": "value"
          }
        }
      }
    }
  },
  "size": 0,
  "fields": [
    {
      "field": "timestamp",
      "format": "date_time"
    }
  ],
  "stored_fields": [
    "*"
  ],
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "timestamp": {
              "gte": "2021-08-04T08:00:00.000",
              "lte": "2021-08-04T09:00:00.000",
              "format": "strict_date_optional_time"
            }
          }
        }
      ],
      "should": [],
      "must_not": []
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [August 17, 2021, 1:59am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/2 "2021-08-17T01:59:09Z")

</div>

Welcome to our community! 😃

> [@scorona85](#):
>
> How can I match the USA 8.00-9.00 and the ITA 8.00-9.00 datas in order to compare the two data from the same period?

You will need to write two queries to handle this.

---

<div class="post-metadata">

**Author:** ![scorona85](https://avatars.discourse-cdn.com/v4/letter/s/d07c76/32.png) [@scorona85](https://discuss.elastic.co/u/scorona85)\
**Post date:** [August 30, 2021, 7:08am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/3 "2021-08-30T07:08:03Z")

</div>

Thanks for your replay. But if I have to do some aggregations on the data (for example sum) how can I do if I have two queries? Do I have to do them "by hand" to the query result?  
My data are:

```auto
"hits" : [
      {
        "_index" : "myindex-000001",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "my_time" : "2021-08-09T08:00:00.000+02:00",
          "value" : 10.0
        }
      },
      {
        "_index" : "myindex-000002",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "my_time" : "2021-08-09T08:00:00.000-06:00",
          "value" : 10.0
        }
      }
    ]

```

and I have try this query for sum but the result is not correct :

```auto
GET /myindex-00000*/_search?size=10000
{
  "aggs": {
    "agg_sum": {
      "date_histogram": {
        "field": "my_time",
        "fixed_interval": "1h"
      },
      "aggs": {
        "aggregazione": {
          "sum": {
            "field": "value"
          }
        }
      }
    }
  },
  "size": 0,
  "fields": [
    {
      "field": "my_time",
      "format": "date_time"
    }
  ],
  "stored_fields": [
    "*"
  ],
  "query": {
    "bool": {
      "filter": [
        {
                "bool": {
                  "should": [
                    {
                      "range": {
                        "my_time": {
                          "gte": "2021-08-09T07:00:00.000+02:00",
                          "lte": "2021-08-09T09:00:00.000+02:00",
                          "format": "strict_date_optional_time"
                          }
                          }
                          
                    },
                    { 
                      "range": {
                        "my_time": {
                          "gte": "2021-08-09T07:00:00.000-06:00",
                          "lte": "2021-08-09T09:00:00.000-06:00",
                          "format": "strict_date_optional_time"
                          }
                          }
                          }
                          ]
                        }
              }
      ]
    }
  }
}

```

The result is:

```auto
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 2,
    "successful" : 2,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 2,
      "relation" : "eq"
    },
    "max_score" : 0.0,
    "hits" : [
      {
        "_index" : "myindex-000001",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.0,
        "fields" : {
          "my_time" : [
            "2021-08-09T06:00:00.000Z"
          ]
        }
      },
      {
        "_index" : "myindex-000002",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 0.0,
        "fields" : {
          "my_time" : [
            "2021-08-09T14:00:00.000Z"
          ]
        }
      }
    ]
  },
  "aggregations" : {
    "agg_sum" : {
      "buckets" : [
        {
          "key_as_string" : "2021-08-09T06:00:00.000Z",
          "key" : 1628488800000,
          "doc_count" : 1,
          "aggregazione" : {
            "value" : 10.0
          }
        },
        {
          "key_as_string" : "2021-08-09T07:00:00.000Z",
          "key" : 1628492400000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-08-09T08:00:00.000Z",
          "key" : 1628496000000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-08-09T09:00:00.000Z",
          "key" : 1628499600000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-08-09T10:00:00.000Z",
          "key" : 1628503200000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-08-09T11:00:00.000Z",
          "key" : 1628506800000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-08-09T12:00:00.000Z",
          "key" : 1628510400000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-08-09T13:00:00.000Z",
          "key" : 1628514000000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-08-09T14:00:00.000Z",
          "key" : 1628517600000,
          "doc_count" : 1,
          "aggregazione" : {
            "value" : 10.0
          }
        }
      ]
    }
  }
}

Thanks for the support

```

---

<div class="post-metadata">

**Author:** ![scorona85](https://avatars.discourse-cdn.com/v4/letter/s/d07c76/32.png) [@scorona85](https://discuss.elastic.co/u/scorona85)\
**Post date:** [September 9, 2021, 9:46am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/4 "2021-09-09T09:46:29Z")

</div>

Here in easy example :

```auto
PUT pippo-000001/_doc/1?refresh
{
  "date": "2021-09-09T08:00:00+02:00",
  "value": 1
}

```

```auto
PUT pippo-000001/_doc/2?refresh
{
  "date": "2021-09-09T08:00:00+00:00",
  "value": 2
}

```

```auto
PUT pippo-000001/_doc/3?refresh
{
  "date": "2021-09-09T08:00:00-06:00",
  "value": 3
}

```

And my query for the aggregation is

```auto
GET pippo-000001/_search?size=0
{
  "aggs": {
    "agg_sum": {
      "date_histogram": {
        "field": "date",
        "fixed_interval": "1h",
        "time_zone": "+2"
      },
      "aggs": {
        "aggregazione": {
          "sum": {
            "field": "value"
          }
        }
      }
    }
  },
  "size": 0,
  "fields": [
    {
      "field": "date",
      "format": "date_time"
    }
  ],
  "stored_fields": [
    "*"
  ],
  "query": {
    "bool": {
      "must": [],
      "filter": [
        {
          "range": {
            "date": {
                "gte": "2021-09-09T00:00:00.000+02:00",
                "lte": "2021-09-09T23:00:00.000-06:00",
              "format": "strict_date_optional_time"
            }
          }
        }
      ]
    }
  }
}

```

But the result is not correct.

```auto
{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : []
  },
  "aggregations" : {
    "agg_sum" : {
      "buckets" : [
        {
          "key_as_string" : "2021-09-09T08:00:00.000+02:00",
          "key" : 1631167200000,
          "doc_count" : 1,
          "aggregazione" : {
            "value" : 1.0
          }
        },
        {
          "key_as_string" : "2021-09-09T09:00:00.000+02:00",
          "key" : 1631170800000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-09-09T10:00:00.000+02:00",
          "key" : 1631174400000,
          "doc_count" : 1,
          "aggregazione" : {
            "value" : 2.0
          }
        },
        {
          "key_as_string" : "2021-09-09T11:00:00.000+02:00",
          "key" : 1631178000000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-09-09T12:00:00.000+02:00",
          "key" : 1631181600000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-09-09T13:00:00.000+02:00",
          "key" : 1631185200000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-09-09T14:00:00.000+02:00",
          "key" : 1631188800000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-09-09T15:00:00.000+02:00",
          "key" : 1631192400000,
          "doc_count" : 0,
          "aggregazione" : {
            "value" : 0.0
          }
        },
        {
          "key_as_string" : "2021-09-09T16:00:00.000+02:00",
          "key" : 1631196000000,
          "doc_count" : 1,
          "aggregazione" : {
            "value" : 3.0
          }
        }
      ]
    }
  }
}

```

I want that at 8:00 the aggregations are the sum of 1,2 and 3.. because the data is added at 8:00 also if in different fuse

---

<div class="post-metadata">

**Author:** ![scorona85](https://avatars.discourse-cdn.com/v4/letter/s/d07c76/32.png) [@scorona85](https://discuss.elastic.co/u/scorona85)\
**Post date:** [September 17, 2021, 12:54pm UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/5 "2021-09-17T12:54:46Z")

</div>

Anyone who can help me?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [September 19, 2021, 10:08pm UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/6 "2021-09-19T22:08:08Z")

</div>

I don't believe Elasticsearch takes timezones into account when it does aggregations like that sorry to say.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [September 20, 2021, 3:43am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/7 "2021-09-20T03:43:42Z")

</div>

I suspect you may need to extract and store the time in the local timezone in a separate field to achieve that.

---

<div class="post-metadata">

**Author:** ![mene94](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mene94/32/94829_2.png) [@mene94](https://discuss.elastic.co/u/mene94)\
**Post date:** [September 21, 2021, 10:54am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/8 "2021-09-21T10:54:07Z")

</div>

Is it possible ignore the timezone doing the aggregation? I meankeep only 08:00:00.000 ignoring +02:00?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [September 21, 2021, 9:33pm UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/9 "2021-09-21T21:33:43Z")

</div>

If you don't pass that in, yes.

---

<div class="post-metadata">

**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\
**Post date:** [September 22, 2021, 5:09am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/10 "2021-09-22T05:09:33Z")

</div>

Timestamps are not stored as strings so you can not simply ignore the timezone and use the time.

---

<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:** [October 20, 2021, 5:09am UTC](https://discuss.elastic.co/t/date-histogram-with-different-timezones-with-elasticsearch-v-7-13-3/280639/11 "2021-10-20T05:09:43Z")

</div>

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