Elasticsearch日期类型、常见时区问题

最新推荐文章于 2024-02-21 13:24:30 发布

原创最新推荐文章于 2024-02-21 13:24:30 发布 · 3.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#elasticsearch #差8小时 #时区问题

DB 专栏收录该内容

4 篇文章

订阅专栏

Date datatype

简介

存储格式

JSON 本身没有date类型，Elasticsearch将设置了时区的date转换为UTC时间，将没有设置时区的date直接设置为UTC时间，以long型时间戳存储。

查询统计

date类型查询，将date转换为long型时间戳查询；

date类型聚合，返回结果格式化date字符串；

ElasticSearch查询、存储，将date类型转换为UTC long型时间戳。

相同格式存储、查询(同是时间戳或同是格式化字符串)，查询时间一致；查询、存储格式不一致，相差8个小时(GMT+08:00与UTC之间相差8小时)

示例

索引

PUT date_index { "settings": { "number_of_shards": 1, "number_of_replicas": 0 }, "mappings": { "test": { "properties": { "date": { "type": "date" } } } } }

查询

格式化date存储,时间戳查询

格式化date存储

POST /_bulk {"index":{"_index":"date_index","_type":"test", "_id":"1"}} {"date":"2020-01-01T00:00:00Z"} {"index":{"_index":"date_index","_type":"test", "_id":"2"}} {"date":"2020-01-01T08:00:00Z"} {"index":{"_index":"date_index","_type":"test", "_id":"3"}} {"date":"2020-01-01T16:00:00Z"} {"index":{"_index":"date_index","_type":"test", "_id":"4"}} {"date":"2020-01-01T23:00:00Z"} {"index":{"_index":"date_index","_type":"test", "_id":"5"}} {"date":"2020-01-02T00:00:00Z"} {"index":{"_index":"date_index","_type":"test", "_id":"6"}} {"date":"2020-01-02T08:00:00Z"} {"index":{"_index":"date_index","_type":"test", "_id":"7"}} {"date":"2020-01-02T16:00:00Z"} {"index":{"_index":"date_index","_type":"test", "_id":"8"}} {"date":"2020-01-02T23:00:00Z"}

时间戳查询

2020-01-01T16:00:00Z(UTC时间 = 东八区-8)

= 2020-01-02 00:00:00(默认东八区时间)

= 2020-01-02T00:00:00Z+08:00(东八区时间)

= 1577894400000

存储格式date类型转换为long型时间戳，查询格式date为long型时间戳，东八区时间戳和UTC时间戳相差8小时

request

POST date_index/_search { "query": { "range": { "date": { "gte": "1577894400000" } } }, "size": 1 }

response

{ "_index" : "date_index", "_type" : "test", "_id" : "3", "_score" : 1.0, "_source" : { "date" : "2020-01-01T16:00:00Z" } }

时间戳存储、格式化date查询

时间戳存储

POST /_bulk {"index":{"_index":"date_index","_type":"test", "_id":"1"}} {"date":"1577980800000"} {"index":{"_index":"date_index","_type":"test", "_id":"2"}} {"date":"1578009600000"} {"index":{"_index":"date_index","_type":"test", "_id":"3"}} {"date":"1578038400000"} {"index":{"_index":"date_index","_type":"test", "_id":"4"}} {"date":"1578063600000"} {"index":{"_index":"date_index","_type":"test", "_id":"5"}} {"date":"1578067200000"} {"index":{"_index":"date_index","_type":"test", "_id":"6"}} {"date":"1578096000000"} {"index":{"_index":"date_index","_type":"test", "_id":"7"}} {"date":"1578124800000"} {"index":{"_index":"date_index","_type":"test", "_id":"8"}} {"date":"1578150000000"}

格式化查询

request

POST date_index/_search { "query": { "range": { "date": { "gte": "2020-01-04T00:00:00Z" } } } }

response

{ "_index" : "date_index", "_type" : "test", "_id" : "6", "_score" : 1.0, "_source" : { "date" : "1578096000000" } }

1578096000000

= 2020-01-04 08:00:00(默认东八区时间)

= 2020-01-04T08:00:00Z+08:00(默认东八区时间)

=2020-01-04T00:00:00Z(UTC时间)

聚合

Elasticserch内存存储都是long型时间戳、UTC时间，想要获取本地时区的统计信息需要指定时区。

request

POST date_index/_search { "size": 0, "aggs": { "by_day": { "date_histogram": { "field": "date", "interval": "day", "time_zone": "+08:00" } } } }

response

{ "by_day" : { "buckets" : [ { "key_as_string" : "2020-01-01T00:00:00.000+08:00", "key" : 1577808000000, "doc_count" : 2 }, { "key_as_string" : "2020-01-02T00:00:00.000+08:00", "key" : 1577894400000, "doc_count" : 4 }, { "key_as_string" : "2020-01-03T00:00:00.000+08:00", "key" : 1577980800000, "doc_count" : 2 } ] } }