Problem with range search with datetime field

Elastic Stack Elasticsearch
1

I have a strange problem when I make queries in date range

Mapping index

. . .
"datetime": {
           "type": "date",
           "format": "epoch_second||yyyy-MM-dd HH:mm:ss",
           "ignore_malformed": true
         },
. . .

Query without range

{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "log_id": {
              "value": 9908016
            }
          }
        },
        {
          "range": {
            "datetime": {
              "gte": 1670581920,
              "time_zone": "Z",
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "log_id"
    },
    {
      "field": "datetime",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

Result

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "max_score": null,
    "hits": [
      {
        "_index": "analyzers-2022111701",
        "_id": "McJx9oQByZyUKY4qIpWk",
        "_score": null,
        "fields": {
          "log_id": [
            "9908016"
          ],
          "datetime": [
            "2022-12-09T10:32:00.000Z"
          ]
        },
        "sort": [
          9697694
        ]
      }
    ]
  }
}

Query with range

{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "log_id": {
              "value": 9908016 
            }
          }
        },
        {
          "range": {
            "datetime": {
              "gte": 1670581920,
              "lte": 1670583333,
              "time_zone": "Z",
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "log_id"
    },
    {
      "field": "datetime",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

Result empty

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "max_score": null,
    "hits": []
  }
}

But if use datetime in format yyyy-MM-dd HH:mm:ss in query works fine.

{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "log_id": {
              "value": 9908016
            }
          }
        },
        {
          "range": {
            "datetime": {
              "gte": "2022-12-09 10:32:00",
              "lte": "2022-12-09 10:55:33",
              "time_zone": "Z",
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "log_id"
    },
    {
      "field": "datetime",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

Good result

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "max_score": null,
    "hits": [
      {
        "_index": "analyzers-2022111701",
        "_id": "McJx9oQByZyUKY4qIpWk",
        "_score": null,
        "fields": {
          "log_id": [
            "9908016"
          ],
          "datetime": [
            "2022-12-09T10:32:00.000Z"
          ]
        },
        "sort": [
          9697694
        ]
      }
    ]
  }
}

I don't understand why this behavior

This forces me to rephrase my calls from my app (PHP) and drives me crazy.

2

Hi @abkrim

Try change "format" to:

"datetime": {
        "type": "date",
        "format": "epoch_second||strict_date_optional_time||yyyy-MM-dd HH:mm:ss",
        "ignore_malformed": true
      }
3

Don't work

Process

  • Create a new index with mapping advised
  • Migrate from old index to new index
  • Assign and verify the alias
  • Verify the mapping
  • Delete the old index
  • Find an item for the test
  • Do the trial and error

New mapping

{
  "analyzers-2021120901": {
    "mappings": {
      "runtime": {
        "total_consumption": {
          "type": "double",
          "script": {
            "source": """
          double sum = 0;
          if (doc['pa1_w'].size() == 0) { sum = sum + 0 } else { sum = sum + doc['pa1_w'].value}
          if (doc['pa2_w'].size() == 0) { sum = sum + 0 } else { sum = sum + doc['pa2_w'].value}
          if (doc['pa3_w'].size() == 0) { sum = sum + 0 } else { sum = sum + doc['pa3_w'].value}
          emit(sum);
        """,
            "lang": "painless"
          }
        }
      },
      "properties": {
        ...
        "datetime": {
          "type": "date",
          "format": "epoch_second||strict_date_optional_time||yyyy-MM-dd HH:mm:ss",
          "ignore_malformed": true
        },
        "log_id": {
          "type": "keyword"
        },
       ...
      }
    }
  }
}

Search one element

GET work-analyzers/_doc/kTEy7oQBksEvPZMu_LNE

{
  "_index": "analyzers-2021120901",
  "_id": "kTEy7oQBksEvPZMu_LNE",
  "_version": 1,
  "_seq_no": 1177,
  "_primary_term": 1,
  "found": true,
  "_source": {
    ...
    "log_id": 6474,
    "datetime": 1668321001,
   ...
  }
}

Try with range

{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "log_id": {
              "value": 6474
            }
          }
        },
        {
          "range": {
            "datetime": {
              "gte": 1668321000,
              "lte": 1668321010,
              "time_zone": "Z",
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "log_id"
    },
    {
      "field": "datetime",
      "format": "strict_date_optional_time_nanos"
    },
    {
      "field": "total_consumption"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

Not results

{
  "took": 1,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "max_score": null,
    "hits": []
  }
}

Try with datetime formated

{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "log_id": {
              "value": 6474
            }
          }
        },
        {
          "range": {
            "datetime": {
              "gte": "2022-11-13 06:30:00",
              "lte": "2022-11-13 06:30:10",
              "time_zone": "Z",
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "log_id"
    },
    {
      "field": "datetime",
      "format": "strict_date_optional_time_nanos"
    },
    {
      "field": "total_consumption"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ],
  "track_total_hits": -1
}

Voila.. get results.

{
  "took": 75,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "max_score": null,
    "hits": [
      {
        "_index": "analyzers-2021120901",
        "_id": "kTEy7oQBksEvPZMu_LNE",
        "_score": null,
        "fields": {
          "log_id": [
            "6474"
          ],
          "datetime": [
            "2022-11-13T06:30:01.000Z"
          ],
          "total_consumption": [
            1963
          ]
        },
        "sort": [
          1177
        ]
      }
    ]
  }
}
4

My tests:

Mapping

PUT idx_test
{
  "mappings": {
    "properties": {
      "datetime": {
        "type": "date",
        "format": "epoch_second||strict_date_optional_time||yyyy-MM-dd HH:mm:ss",
        "ignore_malformed": true
      },
      "log_id": {
        "type": "keyword"
      }
    }
  }
}

Data Index

POST idx_test/_doc
{
  "log_id": 9908016,
  "datetime": "2022-12-09T10:32:00.000Z"
}

POST idx_test/_doc
{
  "log_id": 6474,
  "datetime": 1668321001
}

Try with range

GET idx_test/_search
{
  "fields": [
    {
      "field": "log_id"
    },
    {
      "field": "datetime",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "_source": false, 
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "log_id": {
              "value": 6474
            }
          }
        },
        {
          "range": {
            "datetime": {
              "gte": 1668321000,
              "lte": 1668321010,
              "time_zone": "Z",
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  }
}

Response:

"hits" : [
      {
        "_index" : "idx_test",
        "_type" : "_doc",
        "_id" : "rq8094QB9Y3TepWPHw6d",
        "_score" : 1.6931472,
        "fields" : {
          "log_id" : [
            "6474"
          ],
          "datetime" : [
            "2022-11-13T06:30:01.000Z"
          ]
        }
      }
    ]

Try with datetime formated

GET idx_test/_search
{
  "size": 1,"fields": [
    {
      "field": "log_id"
    },
    {
      "field": "datetime",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "_source": false, 
   "query": {
    "bool": {
      "must": [
        {
          "term": {
            "log_id": {
              "value": 6474
            }
          }
        },
        {
          "range": {
            "datetime": {
              "gte": "2022-11-13 06:30:00",
              "lte": "2022-11-13 06:30:10",
              "time_zone": "Z",
              "boost": 1
            }
          }
        }
      ],
      "boost": 1
    }
  }
}

Response

"hits" : [
      {
        "_index" : "idx_test",
        "_type" : "_doc",
        "_id" : "rq8094QB9Y3TepWPHw6d",
        "_score" : 1.6931472,
        "fields" : {
          "log_id" : [
            "6474"
          ],
          "datetime" : [
            "2022-11-13T06:30:01.000Z"
          ]
        }
      }
    ]
5

Desperate

  • Triple comprobación.
  • Copy & Paste your code and change only unixtime for lte 6 gte, and for log_id.
  • Verify mapping for check datetime format
  • Try in local and in production.
  • Select one doc with a simple search.
  • Use his values for search.
  • With unixtime not work.
  • Version 8.5.3 in all sites.

Also after this test get your data example, and grab a video.

Incredible but it's not working.

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.