记录一下日期查询问题
"format": "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
# 创建索引
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
}
}
}
}
ElasticSearch使用kibana控制台查询示例(带时间范围查询)
#查询transCode为OO06U001，并且根据@timestamp日期范围过滤，求出该交易码的最大、最小、平均耗时
GET transactionmonitor-2021.12.08/doc/_search
{
"query": {
"bool": {
"must": [
{
"match": {
"transCode.keyword": "OO06U001"
}
}
],
"filter": {
"range": {
"@timestamp": {
"gte":"2021-12-08 00:18:00",
"lte":"2021-12-08 00:20:00",
"time_zone":"+08:00",
"format":"yyyy-MM-dd HH:mm:ss"
}
}
}
}
},
"aggs": {
"costTimeMax": {
"max": {
"field": "costTime"
}
},
"costTimeMin":{
"min": {
"field": "costTime"
}
},
"costTimeAvg":{
"avg": {
"field": "costTime"
}
}
}
}
#查询出@timestamp该日期范围内的所有交易码的最大、最小、平均耗时，doc_count出现次数
##filter查询没有相关性得分
GET transactionmonitor-2021.12.08/doc/_search
{
"query": {
"bool": {
"filter": {
"range": {
"@timestamp": {
"gte":"2021-12-08 00:18:00",
"lte":"2021-12-08 00:20:00",
"time_zone":"+08:00",
"format":"yyyy-MM-dd HH:mm:ss"
}
}
}
}
},
"aggs": {
"transCodeTerms": {
"terms": {
"field": "transCode.keyword"
},
"aggs": {
"costTimeMax": {
"max": {
"field": "costTime"
}
},
"costTimeMin":{
"min": {
"field": "costTime"
}
},
"costTimeAvg":{
"avg": {
"field": "costTime"
}
}
}
}
}
}
#查询出交易码OO06U001和@timestamp该日期范围内的最大、最小、平均耗时，doc_count出现次数
#range查询有相关性得分
GET transactionmonitor-2021.12.08/doc/_search
{
"query": {
"bool": {
"must": [
{
"match": {
"transCode": "OO06U001"
}
},
{
"range": {
"@timestamp": {
"gte": "2021-12-08 00:18:00.00",
"lte": "2021-12-08 00:20:00.00",
"time_zone": "+08:00",
"format": "yyyy-MM-dd HH:mm:ss.SS"
}
}
}
]
}
}
,
"aggs": {
"transCodeTerms": {
"terms": {
"field": "transCode.keyword"
},
"aggs": {
"costTimeMax": {
"max": {
"field": "costTime"
}
},
"costTimeMin":{
"min": {
"field": "costTime"
}
},
"costTimeAvg":{
"avg": {
"field": "costTime"
}
}
}
}
}
}
#查询出@timestamp该日期范围内的所有交易码的最大、最小、平均耗时，doc_count出现次数
#在根据出现次数正序排列（asc）
##filter查询没有相关性得分
GET transactionmonitor-2021.12.08/doc/_search
{
"query": {
"bool": {
"filter": {
"range": {
"@timestamp": {
"gte":"2021-12-08 00:18:00",
"lte":"2021-12-08 00:20:00",
"time_zone":"+08:00",
"format":"yyyy-MM-dd HH:mm:ss"
}
}
}
}
},
"aggs": {
"transCodeTerms": {
"terms": {
"field": "transCode.keyword",
"order": {
"_count": "asc"
}
},
"aggs": {
"costTimeMax": {
"max": {
"field": "costTime"
}
},
"costTimeMin":{
"min": {
"field": "costTime"
}
},
"costTimeAvg":{
"avg": {
"field": "costTime"
}
}
}
}
}
}
补充聚合结果排序
#查询出@timestamp该日期范围内的所有交易码的最大、最小、平均耗时，doc_count出现次数
#在根据聚合结果costTimeMin(最小耗时)正序(asc)排列
#filter查询没有相关性得分
GET transactionmonitor-2021.12.08/doc/_search
{
"query": {
"bool": {
"must": [
{"match": {
"isSuccess.keyword": "Y"
}
}
],
"filter": {
"range": {
"@timestamp": {
"gte":"2021-12-08 00:18:00",
"lte":"2021-12-08 00:20:00",
"time_zone":"+08:00",
"format":"yyyy-MM-dd HH:mm:ss"
}
}
}
}
},
"aggs": {
"transCodeTerms": {
"terms": {
"field": "transCode.keyword",
"order": {
"costTimeMin": "asc"
}
},
"aggs": {
"costTimeMax": {
"max": {
"field": "costTime"
}
},
"costTimeMin":{
"min": {
"field": "costTime"
}
},
"costTimeAvg":{
"avg": {
"field": "costTime"
}
}
}
}
}
}
#使用自带 key(分类的字段) 和 count(匹配的次数) 字段排序
#BucketOrder.key(asc) BucketOrder.count(asc) BucketOrder.aggregation(orderField, asc)
GET transactionmonitor-2021.12.08/doc/_search
{
"query": {
"bool": {
"must": [
{"match": {
"isSuccess.keyword": "Y"
}
},
{
"match": {
"transCode.keyword": "DEDE02Q002"
}
}
],
"filter": {
"range": {
"@timestamp": {
"gte":1638893880000,
"lte":1638894000000
}
}
}
}
},
"aggs": {
"transCodeGrp": {
"terms": {
"field": "@timestamp",
"order": {
"_key": "asc",
"_count": "asc"
}
},
"aggs": {
"costTimeAvg":{
"avg": {
"field": "costTime"
}
}
}
}
}
}
