Elasticsearch-日期数据类型和时区详解

作者：AllinToyou | 2024-05-13 21:58:01

踩

前言

本文基于elasticsearch7.3.0版本

在这里插入图片描述

elasticsearch日期数据类型

官方文档:日期数据类型

在elasticsearch内部，日期被转换为UTC(如果指定了时区)，并存储为一个自1997-01-01 00:00:00(GMT)至当前时刻所经过的毫秒数

对日期的查询在内部转换为这种毫秒数表示形式上的范围查询
聚合和存储字段的结果将根据与字段关联的日期格式转换回字符串(聚合和存储字段的日期将始终以字符串形式呈现，即使最初在JSON文档中作为Long提供日期也是如此)

没有指定日期格式

默认使用

"strict_date_optional_time||epoch_millis"
1

官方文档:elasticsearch默认支持的所有日期格式

# 创建索引
PUT my_date
{
  "mappings": {
    "properties": {
      "publicDate":{
        "type": "date",
        // 不管publicDate是什么格式, 存储字段始终是字符串形式, 默认格式为yyyy-MM-dd'T'HH:mm:ss.SSSZ
        "store": true
      },
      "tag":{
        "type": "keyword",
        "store": true
      }
    }
  }
}

# 索引文档
POST my_date/_doc/1
{
  "publicDate":"1576022400000",
  "tag":"0时区:2019-12-11T00:00:00.000Z"
}

POST my_date/_doc/2
{
  "publicDate":"1576051200000",
  "tag":"0时区:2019-12-11T08:00:00.000Z"
}


POST my_date/_doc/3
{
  "publicDate":"2019-12-11T08:00:00",
  "tag":"0时区:2019-12-11T08:00:00.000Z"
}

POST my_date/_doc/4
{
  "publicDate":"2019-12-11T08:00:00+08:00",
  "tag":"0时区:2019-12-11T00:00:00.000Z"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

从_source获取存储的文档, 不做任何处理,原样返回

# 查询
GET my_date/_search
{ 
  "query": {
    "match_all": {}
  }
}

# 结果
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "publicDate" : "1576022400000",
          "tag" : "0时区:2019-12-11T00:00:00.000Z"
        }
      },
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "publicDate" : "1576051200000",
          "tag" : "0时区:2019-12-11T08:00:00.000Z"
        }
      },
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "publicDate" : "2019-12-11T08:00:00",
          "tag" : "0时区:2019-12-11T08:00:00.000Z"
        }
      },
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "4",
        "_score" : 1.0,
        "_source" : {
          "publicDate" : "2019-12-11T08:00:00+08:00",
          "tag" : "0时区:2019-12-11T00:00:00.000Z"
        }
      }
    ]
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69

从存储字段获取文档, 日期会被格式成字符串形式

# 查询
GET my_date/_search
{
  "stored_fields": ["publicDate", "tag"], 
  "query": {
    "match_all": {}
  }
}

# 结果
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "fields" : {
          "publicDate" : [
            "2019-12-11T00:00:00.000Z"
          ],
          "tag" : [
            "0时区:2019-12-11T00:00:00.000Z"
          ]
        }
      },
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "fields" : {
          "publicDate" : [
            "2019-12-11T08:00:00.000Z"
          ],
          "tag" : [
            "0时区:2019-12-11T08:00:00.000Z"
          ]
        }
      },
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "fields" : {
          "publicDate" : [
            "2019-12-11T08:00:00.000Z"
          ],
          "tag" : [
            "0时区:2019-12-11T08:00:00.000Z"
          ]
        }
      },
      {
        "_index" : "my_date",
        "_type" : "_doc",
        "_id" : "4",
        "_score" : 1.0,
        "fields" : {
          "publicDate" : [
            "2019-12-11T00:00:00.000Z"
          ],
          "tag" : [
            "0时区:2019-12-11T00:00:00.000Z"
          ]
        }
      }
    ]
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

聚合文档, 日期会被格式成字符串形式

# 聚合
GET my_date/_search
{
  "size": 0,
  "aggs": {
    "date_aggs": {
      "date_histogram": {
        "field": "publicDate",
        "calendar_interval": "1h",
        // >=1数量的桶才会被展示, 默认是0, 即都展示
        "min_doc_count": 1
      },
      "aggs":{
        "top_aggs":{
        	// 展示每个桶的前10个
          "top_hits": {
            "size": 10
          }
        }
      }
    }
  }
}

# 结果
{
  "took" : 12,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 4,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "date_aggs" : {
      "buckets" : [
        {
          "key_as_string" : "2019-12-11T00:00:00.000Z",
          "key" : 1576022400000,
          "doc_count" : 2,
          "top_aggs" : {
            "hits" : {
              "total" : {
                "value" : 2,
                "relation" : "eq"
              },
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "my_date",
                  "_type" : "_doc",
                  "_id" : "1",
                  "_score" : 1.0,
                  "_source" : {
                    "publicDate" : "1576022400000",
                    "tag" : "0时区:2019-12-11T00:00:00.000Z"
                  }
                },
                {
                  "_index" : "my_date",
                  "_type" : "_doc",
                  "_id" : "4",
                  "_score" : 1.0,
                  "_source" : {
                    "publicDate" : "2019-12-11T08:00:00+08:00",
                    "tag" : "0时区:2019-12-11T00:00:00.000Z"
                  }
                }
              ]
            }
          }
        },
        {
          "key_as_string" : "2019-12-11T08:00:00.000Z",
          "key" : 1576051200000,
          "doc_count" : 2,
          "top_aggs" : {
            "hits" : {
              "total" : {
                "value" : 2,
                "relation" : "eq"
              },
              "max_score" : 1.0,
              "hits" : [
                {
                  "_index" : "my_date",
                  "_type" : "_doc",
                  "_id" : "2",
                  "_score" : 1.0,
                  "_source" : {
                    "publicDate" : "1576051200000",
                    "tag" : "0时区:2019-12-11T08:00:00.000Z"
                  }
                },
                {
                  "_index" : "my_date",
                  "_type" : "_doc",
                  "_id" : "3",
                  "_score" : 1.0,
                  "_source" : {
                    "publicDate" : "2019-12-11T08:00:00",
                    "tag" : "0时区:2019-12-11T08:00:00.000Z"
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

指定日期格式

elasticsearch支持自定义日期格式
多种格式可以通过 || 间隔。在找到匹配的格式之前，将依次尝试每种格式。
第一种格式将用于格式化毫秒值为字符串(聚合和存储字段)

"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
1

# 创建索引
PUT my_date1
{
  "mappings": {
    "properties": {
      "publicDate": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis",
        // 不管publicDate是什么格式, 存储字段始终是字符串形式, 默认格式为第一种格式, 这里为yyyy-MM-ddHH:mm:ss
        // 同理, 如果yyyy-MM-dd在第一个, 那么格式化字符串形式就是yyyy-MM-dd
        "store": true
      }
    }
  }
}

# 索引文档
POST my_date1/_doc/1
{
  "publicDate":"1576051200000"
}

POST my_date1/_doc/2
{
  "publicDate":"2019-12-11 08:00:00"
}

POST my_date1/_doc/3
{
  "publicDate":"2019-12-11"
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

从_source获取存储的文档, 不做任何处理,原样返回

# 查询
GET my_date1/_search
{
  "query": {
    "match_all": {}
  }
}

# 结果
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_date1",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "publicDate" : "1576051200000"
        }
      },
      {
        "_index" : "my_date1",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "publicDate" : "2019-12-11 08:00:00"
        }
      },
      {
        "_index" : "my_date1",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "publicDate" : "2019-12-11"
        }
      }
    ]
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

从存储字段获取文档, 日期会被格式成字符串形式, 第一个格式为yyyy-MM-dd HH:mm:ss

# 查询
GET my_date1/_search
{
  "stored_fields": ["publicDate"], 
  "query": {
    "match_all": {}
  }
}

# 结果
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_date1",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "fields" : {
          "publicDate" : [
            "2019-12-11 08:00:00"
          ]
        }
      },
      {
        "_index" : "my_date1",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "fields" : {
          "publicDate" : [
            "2019-12-11 08:00:00"
          ]
        }
      },
      {
        "_index" : "my_date1",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "fields" : {
          "publicDate" : [
            "2019-12-11 00:00:00"
          ]
        }
      }
    ]
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

聚合文档, 日期会被格式成字符串形式, 第一个格式为yyyy-MM-dd HH:mm:ss

# 聚合
GET my_date1/_search
{
  "size": 0,
  "aggs": {
    "date_aggs": {
      "date_histogram": {
        "field": "publicDate",
        "calendar_interval": "1h",
        // >=1数量的桶才会被展示, 默认是0, 即都展示
        "min_doc_count":1
      }
    }
  }
}

# 结果
{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "date_aggs" : {
      "buckets" : [
        {
          "key_as_string" : "2019-12-11 00:00:00",
          "key" : 1576022400000,
          "doc_count" : 1
        },
        {
          "key_as_string" : "2019-12-11 08:00:00",
          "key" : 1576051200000,
          "doc_count" : 2
        }
      ]
    }
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

elasticsearch时区问题

存入es	解析时区	格式化时区	说明
字符串通过UTC 0时区解析到的时间戳	UTC 0时区	UTC 0时区	解析和格式化时区一致，没有问题
字符串通过东八区解析到的时间戳	东八区	UTC 0时区	解析和格式化时区不一致,数据库导入时间戳到es缺少八小时就是这个原因, 常见, 聚合时需要指定东八区
字符串（没有时区）	UTC 0时区	UTC 0时区	解析和格式化时区一致，没有问题, 推荐使用
字符串（有时区）	指定的时区	UTC 0时区	解析和格式化时区不一致的话，就会存在问题

总结
elasticsearch时区问题产生的根本原因，说白了就是解析和格式化时的时区不一致造成的

数据库日期导入到es，聚合出来缺少八小时原因分析

存入时间戳：

数据库日期字符串yyyy-MM-dd HH:mm:ss
本地解析字符串成Date对象，时区：东八区
getTime()获取时间戳，存入es
格式化日期成字符串，时区：UTC 0时区
解析和格式化时区不一致，所以导致缺少八小时

存入字符串（没有时区）：

数据库日期字符串yyyy-MM-dd HH:mm:ss
直接json写入es，es内部解析成Date对象，时区：UTC 0时区
格式化日期成字符串，时区：UTC 0时区
解析和格式化时区一致，没有问题

备注：
直接存入字符串没有这个问题，存入时间戳时会少八个小时

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/AllinToyou/article/detail/565863