ES版本6.7.1，Java rest-high-level-client版本6.7.2
在es里查询条件date类型的字段时，比如range查询，若格式化设为yyyy-MM-dd HH:mm:ss，则传入的gte等参数默认只接受这个格式的字符串。
如果传入时间戳的long值，则需指定format为epoch_millis”，两种参数意义一样，只在形式不同，但其查询结果却不一致。
- 字符串形式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
GET es_demo/_count { "query": { "bool": { "must": [ { "range": { "createTime": { "gte": "2021-11-12 00:00:00", "lte": "2021-11-13 23:59:59" } } } ] } } }
字符串时间参数统计结果
1
2
3
4
5
6
7
8
9
{
"count": 1112,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
}
}
- 时间戳long值形式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
GET es_demo/_count { "query": { "bool": { "must": [ { "range": { "createTime": { "gte": "1636646400000", "lte": "1636819199000", "format": "epoch_millis" } } } ] } } }
时间戳long值参数统计结果
1
2
3
4
5
6
7
8
9
{
"count": 1580,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
}
}
两者统计结果却并不一致。ES的时区并不能像一般的关系型数据库那样自动转换，所以接下来再在时间戳形式的range里加上time_zone属性，值设置为+0800。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
GET es_demo/_count
{
"query": {
"bool": {
"must": [
{
"range": {
"createTime": {
"gte": "1636646400000",
"lte": "1636819199000",
"format": "epoch_millis",
"time_zone": "+0800"
}
}
}
]
}
}
}
执行结果
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
{
"error": {
"root_cause": [
{
"type": "parse_exception",
"reason": "failed to parse date field [1636646400000] with format [epoch_millis]"
}
],
"type": "search_phase_execution_exception",
"reason": "all shards failed",
"phase": "query",
"grouped": true,
"failed_shards": [
{
"shard": 0,
"index": "es_demo",
"node": "dmgSpYrDRGOAWTgwnxdyEw",
"reason": {
"type": "parse_exception",
"reason": "failed to parse date field [1636646400000] with format [epoch_millis]",
"caused_by": {
"type": "illegal_argument_exception",
"reason": "time_zone must be UTC for format [epoch_millis]"
}
}
}
]
},
"status": 400
}
注意这句time_zone must be UTC for format [epoch_millis]，说明使用epoch_millis时的时区设置必须在+0000。
所以需要变个方式，在时间戳里加上8小时。
- 获取手动+0800(CST)的时间戳
1 2 3 4 5 6 7 8 9 10 11
@Slf4j class EsTest { // time_zone must be UTC for format [epoch_millis] 使用epoch_millis格式查询date类型时需要加8小时 @Test void plus8hByHand() { log.info("" + LocalDateTimeUtil.toEpochMilli(LocalDateTime.of(2021, 11, 12, 0, 0, 0).plusHours(8))); log.info("" + LocalDateTimeUtil.toEpochMilli(LocalDateTime.of(2021, 11, 13, 23, 59, 59).plusHours(8))); } }
使用新时间戳参数DSL查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
GET es_demo/_count
{
"query": {
"bool": {
"must": [
{
"range": {
"createTime": {
"gte": "1636675200000",
"lte": "1636847999000",
"format": "epoch_millis"
}
}
}
]
}
}
}
执行结果
1
2
3
4
5
6
7
8
9
{
"count": 1112,
"_shards": {
"total": 5,
"successful": 5,
"skipped": 0,
"failed": 0
}
}
此时结果一致。
- Java rest-high-level-client API 条件构造
1
2
3
4
QueryBuilders.rangeQuery("createTime")
.gte(LocalDateTimeUtil.toEpochMilli(LocalDateTimeUtil.parse("2021-11-12 00:00:00", DatePattern.NORM_DATETIME_PATTERN).plusHours(8)))
.lte(LocalDateTimeUtil.toEpochMilli(LocalDateTimeUtil.parse("2021-11-13 23:59:59", DatePattern.NORM_DATETIME_PATTERN).plusHours(8)))
.format("epoch_millis")
文中出现的工具类来自hutool或apache.commons