elasticsearch之日期型別有點怪

無風聽海發表於2023-02-15

一、Date型別簡介

elasticsearch透過JSON格式來承載資料的，而JSON中是沒有Date對應的資料型別的，但是elasticsearch可以透過以下三種方式處理JSON承載的Date資料

符合特定格式化的日期字串；
基於milliseconds-since-the-epoch的一個長整型數字；
基於seconds-since-the-epoch的一個長整型數字；

索引資料的時候，elasticsearch內部會基於UTC時間，將傳入的資料轉化為基於milliseconds-since-the-epoch的一個長整型數字；查詢資料的時候，elasticsearch內部會將查詢轉化為range查詢；

二、測試資料準備

建立mapping，設定create_date的type為date

PUT my_date_index
{
  "mappings": {
    "_doc": {
      "properties": {
        "create_date": {
          "type": "date" 
        }
      }
    }
  }
}

索引以下三個document

PUT my_date_index/_doc/1
{ "create_date": "2015-01-01" } 

PUT my_date_index/_doc/2
{ "create_date": "2015-01-01T12:10:30Z" } 

PUT my_date_index/_doc/3
{ "create_date": 1420070400001 }

三、日期查詢的詭異之處

我們希望可以透過以下查詢命中2015-01-01的記錄

POST my_date_index/_search
{
  "query": {
    "term": {
      "create_date": "2015-01-01"
    }
  }
}

檢視執行結果發現命中了三條資料

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 3,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_date_index",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "create_date" : "2015-01-01T12:10:30Z"
        }
      },
      {
        "_index" : "my_date_index",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "create_date" : "2015-01-01"
        }
      },
      {
        "_index" : "my_date_index",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "create_date" : 1420070400001
        }
      }
    ]
  }
}

透過以下可以看到elasticsearch內部確實將查詢重寫為一個範圍查詢create_date:[1420070400000 TO 1420156799999]

POST my_date_index/_search
{
  "profile": "true", 
  "query": {
    "term": {
      "create_date": "2015-01-01"
    }
  }
}


  {
    "id" : "[eD2KQtMGSla7jzJQBQVAfQ][my_date_index][0]",
    "searches" : [
      {
        "query" : [
          {
            "type" : "IndexOrDocValuesQuery",
            "description" : "create_date:[1420070400000 TO 1420156799999]",
            "time_in_nanos" : 2101,
            "breakdown" : {
              "score" : 0,
              "build_scorer_count" : 0,
              "match_count" : 0,
              "create_weight" : 2100,
              "next_doc" : 0,
              "match" : 0,
              "create_weight_count" : 1,
              "next_doc_count" : 0,
              "score_count" : 0,
              "build_scorer" : 0,
              "advance" : 0,
              "advance_count" : 0
            }
          }
        ],
        "rewrite_time" : 2200,
        "collector" : [
          {
            "name" : "CancellableCollector",
            "reason" : "search_cancelled",
            "time_in_nanos" : 700,
            "children" : [
              {
                "name" : "SimpleTopScoreDocCollector",
                "reason" : "search_top_hits",
                "time_in_nanos" : 200
              }
            ]
          }
        ]
      }
    ],
    "aggregations" : [ ]
  }

接下來我們來分析一下Date資料型別的term查詢

我們可以看到termQuery查詢直接呼叫了rangeQuery,並將傳入的日期引數作為range的兩個範圍值；

DateFieldType

@Override
public Query termQuery(Object value, @Nullable QueryShardContext context) {
    Query query = rangeQuery(value, value, true, true, ShapeRelation.INTERSECTS, null, null, context);
    if (boost() != 1f) {
        query = new BoostQuery(query, boost());
    }
    return query;
}

rangeQuery中會呼叫parseToMilliseconds計算查詢的兩個範圍值

DateFieldType

@Override
public Query rangeQuery(Object lowerTerm, Object upperTerm, boolean includeLower, boolean includeUpper, ShapeRelation relation,
                        @Nullable DateTimeZone timeZone, @Nullable DateMathParser forcedDateParser, QueryShardContext context) {
    failIfNotIndexed();
    if (relation == ShapeRelation.DISJOINT) {
        throw new IllegalArgumentException("Field [" + name() + "] of type [" + typeName() +
                "] does not support DISJOINT ranges");
    }
    DateMathParser parser = forcedDateParser == null
            ? dateMathParser
            : forcedDateParser;
    long l, u;
    if (lowerTerm == null) {
        l = Long.MIN_VALUE;
    } else {
        l = parseToMilliseconds(lowerTerm, !includeLower, timeZone, parser, context);
        if (includeLower == false) {
            ++l;
        }
    }
    if (upperTerm == null) {
        u = Long.MAX_VALUE;
    } else {
        u = parseToMilliseconds(upperTerm, includeUpper, timeZone, parser, context);
        if (includeUpper == false) {
            --u;
        }
    }
    Query query = LongPoint.newRangeQuery(name(), l, u);
    if (hasDocValues()) {
        Query dvQuery = SortedNumericDocValuesField.newSlowRangeQuery(name(), l, u);
        query = new IndexOrDocValuesQuery(query, dvQuery);
    }
    return query;
}

透過以下程式碼可以看到，左邊界的值會覆蓋new MutableDateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeZone.UTC)對應的位置的數字，右邊界的值會覆蓋ew MutableDateTime(1970, 1, 1, 23, 59, 59, 999, DateTimeZone.UTC)對應位置的數字；所以我們查詢中輸入2015-01-01，相當於查詢這一天之內的所有記錄；

JodaDateMathParser

private long parseDateTime(String value, DateTimeZone timeZone, boolean roundUpIfNoTime) {
    DateTimeFormatter parser = dateTimeFormatter.parser;
    if (timeZone != null) {
        parser = parser.withZone(timeZone);
    }
    try {
        MutableDateTime date;
        // We use 01/01/1970 as a base date so that things keep working with date
        // fields that are filled with times without dates
        if (roundUpIfNoTime) {
            date = new MutableDateTime(1970, 1, 1, 23, 59, 59, 999, DateTimeZone.UTC);
        } else {
            date = new MutableDateTime(1970, 1, 1, 0, 0, 0, 0, DateTimeZone.UTC);
        }
        final int end = parser.parseInto(date, value, 0);
        if (end < 0) {
            int position = ~end;
            throw new IllegalArgumentException("Parse failure at index [" + position + "] of [" + value + "]");
        } else if (end != value.length()) {
            throw new IllegalArgumentException("Unrecognized chars at the end of [" + value + "]: [" + value.substring(end) + "]");
        }
        return date.getMillis();
    } catch (IllegalArgumentException e) {
        throw new ElasticsearchParseException("failed to parse date field [{}] with format [{}]", e, value,
            dateTimeFormatter.pattern());
    }
}

一般我們使用的日期都是精確到秒，那麼只要我們將輸入資料精確到秒基本上就可以命中記錄；如果還是命中多個記錄，那麼就需要將資料的精度提高到毫秒，並且查詢輸入的時候也需要帶上毫秒；

POST my_date_index/_search
{
  "query": {
    "term": {
      "create_date": "2015-01-01T12:10:30Z"
    }
  }
}

{
  "took" : 10,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 1,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_date_index",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "create_date" : "2015-01-01T12:10:30Z"
        }
      }
    ]
  }
}

四、自定義時間字串的解析格式

elasticsearch中date預設的日期格式是表徵epoch_millis的長整型數字或者符合strict_date_optional_time格式的字串；

public static final DateFormatter DEFAULT_DATE_TIME_FORMATTER = DateFormatter.forPattern("strict_date_optional_time||epoch_millis");

strict_date_optional_time
strict限制時間字串中的年月日部分必須是4、2、2個數字，不足部分在前邊補0，例如20230123；
date_optional_time則要求字串可以不包含時間部分，但是必須包含日期部分；

strict_date_optional_time支援的完整的時間格式如下

 date-opt-time     = date-element ['T' [time-element] [offset]]
 date-element      = std-date-element | ord-date-element | week-date-element
 std-date-element  = yyyy ['-' MM ['-' dd]]
 ord-date-element  = yyyy ['-' DDD]
 week-date-element = xxxx '-W' ww ['-' e]
 time-element      = HH [minute-element] | [fraction]
 minute-element    = ':' mm [second-element] | [fraction]
 second-element    = ':' ss [fraction]
 fraction          = ('.' | ',') digit+

我們使用2015/01/01搜尋的時候，elasticsearch無法解析就會報錯

POST my_date_index/_search
{
  "profile": "true", 
  "query": {
    "term": {
      "create_date": "2015/01/01"
    }
  }
}

{
  "error": {
    "root_cause": [
      {
        "type": "parse_exception",
        "reason": "failed to parse date field [2015/01/01] with format [strict_date_optional_time||epoch_millis]"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "my_date_index",
        "node": "eD2KQtMGSla7jzJQBQVAfQ",
        "reason": {
          "type": "query_shard_exception",
          "reason": "failed to create query: {\n  \"term\" : {\n    \"create_date\" : {\n      \"value\" : \"2015/01/01\",\n      \"boost\" : 1.0\n    }\n  }\n}",
          "index_uuid": "9MTRkZcMTnK8GgK9vKwUuA",
          "index": "my_date_index",
          "caused_by": {
            "type": "parse_exception",
            "reason": "failed to parse date field [2015/01/01] with format [strict_date_optional_time||epoch_millis]",
            "caused_by": {
              "type": "illegal_argument_exception",
              "reason": "Unrecognized chars at the end of [2015/01/01]: [/01/01]"
            }
          }
        }
      }
    ],
    "caused_by": {
      "type": "parse_exception",
      "reason": "failed to parse date field [2015/01/01] with format [strict_date_optional_time||epoch_millis]",
      "caused_by": {
        "type": "illegal_argument_exception",
        "reason": "Unrecognized chars at the end of [2015/01/01]: [/01/01]"
      }
    }
  },
  "status": 400
}

我們可以在mapping或者在搜尋的時候指定format

POST my_date_index/_search
{
    "query": {
        "range" : {
            "create_date" : {
                "gte": "2015/01/01",
                "lte": "2015/01/01",
                "format": "yyyy/MM/dd"
            }
        }
    }
}

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 5,
    "successful" : 5,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : 3,
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "my_date_index",
        "_type" : "_doc",
        "_id" : "2",
        "_score" : 1.0,
        "_source" : {
          "create_date" : "2015-01-01T12:10:30Z"
        }
      },
      {
        "_index" : "my_date_index",
        "_type" : "_doc",
        "_id" : "1",
        "_score" : 1.0,
        "_source" : {
          "create_date" : "2015-01-01"
        }
      },
      {
        "_index" : "my_date_index",
        "_type" : "_doc",
        "_id" : "3",
        "_score" : 1.0,
        "_source" : {
          "create_date" : 1420070400001
        }
      }
    ]
  }
}

日期型別有問題，如何解決？
2020-04-04
型別
ORACLE日期型別
2018-05-03
Oracle型別
Elasticsearch Mapping型別修改
2023-02-17
ElasticsearchAPP型別
MongoDB日期型別查詢
2018-04-03
MongoDB型別
節點型別有Follower、Candidate、Leader
2022-02-28
型別
Python基本資料型別之浮點型
2019-02-16
Python資料型別
【Hive】日期從整形轉為Date型別
2018-07-28
Hive型別
mysql儲存日期使用什麼型別
2021-09-11
MySql型別
XSD 日期和時間資料型別
2023-01-25
資料型別
Elasticsearch資料型別及其屬性
2018-12-30
Elasticsearch資料型別
ElasticSearch7.3 學習之Mapping核心資料型別及dynamic mapping
2022-03-19
ElasticsearchAPP資料型別
Salesforce LWC學習(四十四) Datatable 顯示日期型別的有趣點思考
2023-05-13
Salesforce型別
匯入sql時報日期型別錯誤
2020-04-05
SQL型別
SQL Server日期資料型別DATE的使用
2021-09-09
SQLServer資料型別
ElasticSearch之叢集中的節點
2024-10-05
Elasticsearch
實戰Elasticsearch6的join型別
2022-09-07
Elasticsearch型別
python不同資料型別之間的區別有什麼？
2022-05-24
Python資料型別
HTML5有哪些儲存型別？它們之間有什麼區別？
2024-11-27
HTML型別
Mysql日期（DATE, TIME, DATETIME, TIMESTAMP）型別的比較
2019-03-05
MySql型別
資料型別: 資料型別有哪些？
2021-02-01
資料型別
一對一視訊原始碼，通過日期字串轉換日期型別格式
2021-11-23
原始碼字串型別
Elasticsearch 之 Filter 與 Query 有啥不同？
2021-02-05
ElasticsearchFilter
有關字串元素統計型別題目的一點感想
2020-12-05
字串型別
Mysql 資料型別之整數型別
2020-10-27
MySQL 資料型別
Cypress系列（18）- 可操作型別的命令之點選命令
2020-06-12
型別
TS系列之型別
2019-02-16
型別
操作型別之字串
2018-12-02
型別字串
引用型別之Object
2020-10-06
型別Object
Python之字元型別
2024-11-08
Python字元型別
js基本語法之值型別(資料型別)(變數型別)
2018-08-11
JS資料型別變數
JavaScript nodeType節點型別
2018-09-25
JavaScript型別
型別轉換注意點
2020-11-06
型別
Zookeeper--節點型別
2020-12-07
型別
海外代理有哪些型別？
2022-01-13
型別
公共映象有哪些型別
2022-11-25
型別
【深度思考】JDK8中日期型別該如何使用？
2020-06-12
JDK型別
面試官問：ZooKeeper 有幾種節點型別？別再說 4 種啦！
2021-05-27
面試型別
js資料型別之基本資料型別和引用資料型別
2018-06-19
JS資料型別

elasticsearch之日期型別有點怪

相關文章