发现问题
- 创建索引,配置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-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的日期类型数据,必须全部带上时区信息或者使用时间戳(可视化效果差,不建议)
- 强制:使用日期聚合时,必须带上时区信息
- 强制:查询日期类型数据是,必须带上时区信息,或者时间戳(可视化效果差,不建议)
索引中的文档存在跨时区,那么索引,聚合,查询时都需要带上时区信息
索引中的文档不存在跨时区,即全在同一时区,那么索引,聚合,查询时都不需要带上时区信息