1

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:

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

    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": []
    }
  }
}

Thanks in advance for the response.

scorona85
  • 97
  • 6
  • When you say you want to "match" them - what result are you expecting? Are you expecting 8:00-9:00 in whatever local time the record was in? Or are you expecting the timestamps to be treated as their actual point in time, converting them to a UTC or a specific time zone? The latter is easier, and can be handled by putting a `Z` or an offset at the end of your query timestamps, or by [using the `time_zone` parameter in the range query](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-range-query.html#range-query-time-zone). – Matt Johnson-Pint Aug 04 '21 at 17:07
  • The first one, I aspect 8.00-9:00 in whatever local time the record. And with a single Query the results of USA and ITA from 8:00 - 9:00 (the datas recorded in USA at 8:00-9:00 and ITA at 8:00-9:00). – scorona85 Aug 05 '21 at 05:42

0 Answers0