Elasticsearch時區問題

發現問題

建立索引,配置date類型字段
批量插入資料,格式分别為帶時區資訊,不帶時區資訊,時間戳
對資料進行聚合
對資料進行查詢,分别使用帶時區資訊,不帶時區資訊,時間戳三種格式

建立索引,配置字段類型

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-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的日期類型資料,必須全部帶上時區資訊或者使用時間戳(可視化效果差,不建議)
強制:使用日期聚合時,必須帶上時區資訊
強制:查詢日期類型資料是,必須帶上時區資訊,或者時間戳(可視化效果差,不建議)

Elasticsearch時區問題

發現問題

原因分析

總結

使用建議

繼續閱讀

mysql5.7的sql優化

線程通信和程序通信差別（線程程序差別）

Matlab随機波動率SV、GARCH用MCMC馬爾可夫鍊蒙特卡羅方法分析匯率時間序列

微信小程式前端解密擷取使用者資訊

Spring MVC 自學雜記（五） -- SpringMVC與前台的json資料互動

《MySQL技術内幕：InnoDB存儲引擎》筆記

擴容TIKV節點遇到的坑

延雲行業搜尋資料庫在大資料生态中位置和重要性大資料的挑戰大資料技術的現狀延雲行業搜尋資料庫

PHP輔導代做程式設計：CS353 Database System

自學Zabbix3.10.2-事件通知Notifications upon events-Actions報警配置點選傳回：自學zabbix集錦

HDU 5678 ztr loves trees

拓端tecdat|R語言彈性網絡Elastic Net正則化懲罰回歸模型交叉驗證可視化

二叉樹及其應用--二叉樹建立

尚矽谷—韓順平—圖解 Java設計模式（結構型）（55～）

30天了解30種技術系列---(10)面向Cloud的搜尋引擎 ElasticSearch

詳解STM32單片機的堆棧