Elasticsearch时区问题

﹏ヽ暗。殇╰゛Y 2021-12-08 21:04 876阅读 0赞

## 发现问题 ##

1.  创建索引,配置date类型字段
2.  批量插入数据,格式分别为带时区信息,不带时区信息,时间戳
3.  对数据进行聚合
4.  对数据进行查询,分别使用带时区信息,不带时区信息,时间戳三种格式

**创建索引,配置字段类型**

PUT testdate
    {
      "mappings": {
        "testdate":{
          "properties": {
            "tag":{
              "type": "keyword"
            },
            "datetime":{
              "type": "date"
            }
          }
        }
      }
    }

**批量插入数据**

POST /_bulk
    {"create":{"_index":"testdate","_type":"testdate","_id":1}}
    {"tag":"带时区信息","datetime":"2019-07-15T08:00:00+08:00"}
    {"create":{"_index":"testdate","_type":"testdate","_id":2}}
    {"tag":"不带时区信息","datetime":"2019-07-15T08:00:00"}
    {"create":{"_index":"testdate","_type":"testdate","_id":3}}
    {"tag":"时间戳","datetime":"1563148800000"}

**对数据进行聚合操作**  
不带时区信息聚合

POST /testdate/testdate/_search
    {
      "size": 0, 
      "aggs": {
        "aggs_datetime": {
          "date_histogram": {
            "field": "datetime",
            "interval": "hour"
          },
          "aggs": {
            "aggs_tag": {
              "terms": {
                "field": "tag",
                "size": 10
              }
            }
          }
        }
      }
    }

结果  
不带时区信息时间显示正常, 时间戳和带时区信息时间提前8小时

{
      "took": 2,
      "timed_out": false,
      "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": 3,
        "max_score": 0,
        "hits": []
      },
      "aggregations": {
        "aggs_datetime": {
          "buckets": [
            {
              "key_as_string": "2019-07-15T00:00:00.000Z",
              "key": 1563148800000,
              "doc_count": 2,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "带时区信息",
                    "doc_count": 1
                  },
                  {
                    "key": "时间戳",
                    "doc_count": 1
                  }
                ]
              }
            },
            {
              "key_as_string": "2019-07-15T01:00:00.000Z",
              "key": 1563152400000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T02:00:00.000Z",
              "key": 1563156000000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T03:00:00.000Z",
              "key": 1563159600000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T04:00:00.000Z",
              "key": 1563163200000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T05:00:00.000Z",
              "key": 1563166800000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T06:00:00.000Z",
              "key": 1563170400000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T07:00:00.000Z",
              "key": 1563174000000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T08:00:00.000Z",
              "key": 1563177600000,
              "doc_count": 1,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "不带时区信息",
                    "doc_count": 1
                  }
                ]
              }
            }
          ]
        }
      }
    }

东八区

POST /testdate/testdate/_search
    {
      "size": 0, 
      "aggs": {
        "aggs_datetime": {
          "date_histogram": {
            "field": "datetime",
            "interval": "hour",
            "time_zone": "+08:00"
          },
          "aggs": {
            "aggs_tag": {
              "terms": {
                "field": "tag",
                "size": 10
              }
            }
          }
        }
      }
    }

结果  
时间戳和带时区信息时间显示正常, 不带时区信息时间推后8小时

{
      "took": 3,
      "timed_out": false,
      "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": 3,
        "max_score": 0,
        "hits": []
      },
      "aggregations": {
        "aggs_datetime": {
          "buckets": [
            {
              "key_as_string": "2019-07-15T08:00:00.000+08:00",
              "key": 1563148800000,
              "doc_count": 2,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "带时区信息",
                    "doc_count": 1
                  },
                  {
                    "key": "时间戳",
                    "doc_count": 1
                  }
                ]
              }
            },
            {
              "key_as_string": "2019-07-15T09:00:00.000+08:00",
              "key": 1563152400000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T10:00:00.000+08:00",
              "key": 1563156000000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T11:00:00.000+08:00",
              "key": 1563159600000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T12:00:00.000+08:00",
              "key": 1563163200000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T13:00:00.000+08:00",
              "key": 1563166800000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T14:00:00.000+08:00",
              "key": 1563170400000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T15:00:00.000+08:00",
              "key": 1563174000000,
              "doc_count": 0,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": []
              }
            },
            {
              "key_as_string": "2019-07-15T16:00:00.000+08:00",
              "key": 1563177600000,
              "doc_count": 1,
              "aggs_tag": {
                "doc_count_error_upper_bound": 0,
                "sum_other_doc_count": 0,
                "buckets": [
                  {
                    "key": "不带时区信息",
                    "doc_count": 1
                  }
                ]
              }
            }
          ]
        }
      }
    }

**对数据进行结构化查询**  
时间戳

POST /testdate/testdate/_search
    {
      "query": {
        "range": {
          "datetime": {
            "gte": 1563148800000
          }
        }
      }
    }

三条数据全部返回

{
      "took": 2,
      "timed_out": false,
      "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": 3,
        "max_score": 1,
        "hits": [
          {
            "_index": "testdate",
            "_type": "testdate",
            "_id": "2",
            "_score": 1,
            "_source": {
              "tag": "不带时区信息",
              "datetime": "2019-07-15T08:00:00"
            }
          },
          {
            "_index": "testdate",
            "_type": "testdate",
            "_id": "1",
            "_score": 1,
            "_source": {
              "tag": "带时区信息",
              "datetime": "2019-07-15T08:00:00+08:00"
            }
          },
          {
            "_index": "testdate",
            "_type": "testdate",
            "_id": "3",
            "_score": 1,
            "_source": {
              "tag": "时间戳",
              "datetime": "1563148800000"
            }
          }
        ]
      }
    }

不带时区

POST /testdate/testdate/_search
    {
      "query": {
        "range": {
          "datetime": {
            "gte": "2019-07-15T08:00:00"
          }
        }
      }
    }

只返回了不带时区信息的一条数据

{
      "took": 1,
      "timed_out": false,
      "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": 1,
        "max_score": 1,
        "hits": [
          {
            "_index": "testdate",
            "_type": "testdate",
            "_id": "2",
            "_score": 1,
            "_source": {
              "tag": "不带时区信息",
              "datetime": "2019-07-15T08:00:00"
            }
          }
        ]
      }
    }

带时区

POST /testdate/testdate/_search
    {
      "query": {
        "range": {
          "datetime": {
            "gte": "2019-07-15T08:00:00+08:00"
          }
        }
      }
    }

三条数据全部返回

{
      "took": 0,
      "timed_out": false,
      "_shards": {
        "total": 5,
        "successful": 5,
        "skipped": 0,
        "failed": 0
      },
      "hits": {
        "total": 3,
        "max_score": 1,
        "hits": [
          {
            "_index": "testdate",
            "_type": "testdate",
            "_id": "2",
            "_score": 1,
            "_source": {
              "tag": "不带时区信息",
              "datetime": "2019-07-15T08:00:00"
            }
          },
          {
            "_index": "testdate",
            "_type": "testdate",
            "_id": "1",
            "_score": 1,
            "_source": {
              "tag": "带时区信息",
              "datetime": "2019-07-15T08:00:00+08:00"
            }
          },
          {
            "_index": "testdate",
            "_type": "testdate",
            "_id": "3",
            "_score": 1,
            "_source": {
              "tag": "时间戳",
              "datetime": "1563148800000"
            }
          }
        ]
      }
    }

## 原因分析 ##

/** * @desc : 时间戳 * @author : cheng * @date : 2019-12-10 23:23 */
        @Test
        public void testA() throws Exception{ 
            String dateStr = "2019-07-15 08:00:00";
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
            // 解析字符串，时区：东八区
            Date date = dateFormat.parse(dateStr);
            System.out.println(date.getTime());
    
            // 格式化日期，时区：0时区
            dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
            System.out.println(dateFormat.format(date));
        }
    
    	// 输出
    	1563148800000
    	2019-07-15 00:00:00

/** * @desc : 带时区 * @author : cheng * @date : 2019-12-10 23:24 */
        @Test
        public void testB() throws Exception{ 
            String dateStr = "2019-07-15 08:00:00+0800";
    
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ssZ");
    
            // 解析字符串，时区：东八区
            Date date = dateFormat.parse(dateStr);
    
            // 格式化日期，时区：0时区
            dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
            System.out.println(dateFormat.format(date));
        }
    
    	// 输出
    	2019-07-15 00:00:00+0000

/** * @desc : 不带时区信息 * @author : cheng * @date : 2019-12-10 23:23 */
        @Test
        public void testC() throws Exception{ 
            String dateStr = "2019-07-15 08:00:00";
    
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
            // 解析字符串，时区：0时区
            dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
            Date date = dateFormat.parse(dateStr);
    
            // 格式化日期，时区：0时区
            dateFormat.setTimeZone(TimeZone.getTimeZone("GMT"));
            System.out.println(dateFormat.format(date));
        }
    
    	// 输出
    	2019-07-15 08:00:00

***ES日期类型使用UTC***

**时间戳1563148800000**  
*时间戳本身就是UTC毫秒数, 和时区没有关系, 实际存储的就是这个值*

String dateStr = "2019-07-15 08:00:00";
    // 时间戳和时区无关, UTC毫秒数
    System.out.println(DateUtils.parseDate(dateStr, Locale.ENGLISH, "yyyy-MM-dd HH:mm:ss").getTime());
    System.out.println(DateUtils.parseDate(dateStr, Locale.US, "yyyy-MM-dd HH:mm:ss").getTime());
    System.out.println(DateUtils.parseDate(dateStr, Locale.FRENCH, "yyyy-MM-dd HH:mm:ss").getTime());
    
    输出
    1563148800000
    1563148800000
    1563148800000

**不带时区信息格式2019-07-15T08:00:00**  
*等价于2019-07-15T08:00:00+00:00, 默认就是UTC 0时区时间, 实际存储的就是这个值*

**带时区信息格式2019-07-15T08:00:00+08:00**  
东八区时间 = UTC0时区 + 8个小时  
所以实际存储的值为UTC0时区 = 东八区 - 8小时 = 2019-07-15T00:00:00+00:00, 实际存储2019-07-15T00:00:00+00:00

// 本地时间为2019-07-15 08:00:00
    Date date = new Date();
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    
    // 不设置时区, 默认为机器所在时区
    System.out.println(sdf.format(date));
    
    // 东八区
    sdf.setTimeZone(TimeZone.getTimeZone("GMT+8"));
    System.out.println(sdf.format(date));
    
    // 0时区 = 东八区 - 8个小时
    sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
    System.out.println(sdf.format(date));
    
    输出
    2019-07-15 08:00:00
    2019-07-15 08:00:00
    2019-07-15 00:00:00

## 总结 ##

*  存储:es使用UTC时间存储date类型
 *  查询:查询的时间会转换成UTC时间,然后进行查询操作

## 使用建议 ##

*  强制:存入es的日期类型数据,必须全部带上时区信息或者使用时间戳(可视化效果差,不建议)
 *  强制:使用日期聚合时,必须带上时区信息
 *  强制:查询日期类型数据是,必须带上时区信息,或者时间戳(可视化效果差,不建议)

**索引中的文档存在跨时区,那么索引,聚合,查询时都需要带上时区信息  
索引中的文档不存在跨时区,即全在同一时区,那么索引,聚合,查询时都不需要带上时区信息**