Cannot get date math to work properly in Elasticsearch

jgorlin · August 4, 2019, 1:09am

Hi Team,

I've bene struggling for hours on what seems to me should be a very basic search. I'm trying to find all documents that have a date within the last 7 days:

GET date_test/_search 
{
	"query": {
		"range": {
			"deviceRegistration.ConnectionStatusLastChangeTime": {
				"gte": "now-7d"
			}
		}
	}
}

The field mapping is defined as follows:

"ConnectionStatusLastChangeTime": {
	  "type": "date",
     "format": "strict_date_time"
   }

Yet for some reason, my search is still turning up results that are false, such as:

"ConnectionStatusLastChangeTime" : "2019-02-11T17:49:12.617Z",

There are some correct results, but there are also false results and I can't understand why. Any thoughts on what to check?

spinscale · August 5, 2019, 10:03am

hey,

first, what version of Elasticsearch are you using? Second, can you provide a full example, that can be reproduced locally? Also, please make sure that you samples are valid. The search request contains spaces and JSON in the first line and one would not be able to execute that one properly in kibana. Thank you!

jgorlin · August 5, 2019, 1:33pm

Thanks for your reply. I'm running 7.1.0. I've fixed the search request above, looks like it was a copy/paste error.

Since I have customer PII data, I can't share a full document. I guess my question may be more theoretical - should this query above work correctly when date format is strict_date_time? Are there any known issues or limitations here?

What would be my next level to debug why the date time search using now isn't providing accurate results? Thanks.

spinscale · August 6, 2019, 7:55am

hey,

can you provide a minimal reproducible example. A document that just contains this field, a field mapping properly configured and the query would be sufficient.

In general, this document does not look like it should be returned, the same applies for the strict date as well, but this is nearly impossible without diving deeper, to figure out if this is a bug or a configuration issue.

--Alex

jgorlin · August 6, 2019, 1:26pm

Thanks Alex for your help. I've extracted just that one field and started with a brand new index. I'm still seeing the date search not working properly.

Here is index creation with mapping:

PUT date_test
{
	"mappings": {
		"properties": {
					"ConnectionStatusLastChangeTime": {
						"type": "date",
						"format": "strict_date_time"
					}
		}
	}
}

I've loaded it up with my date dataset. You can download a copy here: dateData.json

Here is my search query:

GET date_test/_search 
{
	"query": {
		"range": {
			"ConnectionStatusLastChangeTime": {
				"gte": "now-7d"
			}
		}
	}
}

Here is the search output (notice the two dates that are much farther back than 7 days - 2 out of 10 results ):

{
  "took" : 1,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 8145,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "lmOyhGEBQgB2YO0Vai_8",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-03T17:04:11.298Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "l2OyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-02T14:29:54.42Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "mGOyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-02T02:31:46.726Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "mWOyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-02-11T17:49:12.617Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "mmOyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-03T17:03:21.537Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "m2OyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-05T18:08:32.589Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "nGOyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-06T03:46:32.062Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "nWOyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2018-08-16T20:21:46.974Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "nmOyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-06T04:49:58.553Z"
        }
      },
      {
        "_index" : "date_test",
        "_type" : "_doc",
        "_id" : "n2OyhGEBQgB2YO0Vay8V",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-02T05:22:42.549Z"
        }
      }
    ]
  }
}

Here is my ES info:

{
  "name" : "hx-es-node-1",
  "cluster_name" : "REDACTED",
  "cluster_uuid" : "REDACTED",
  "version" : {
    "number" : "7.1.0",
    "build_flavor" : "default",
    "build_type" : "deb",
    "build_hash" : "606a173",
    "build_date" : "2019-05-16T00:43:15.323135Z",
    "build_snapshot" : false,
    "lucene_version" : "8.0.0",
    "minimum_wire_compatibility_version" : "6.8.0",
    "minimum_index_compatibility_version" : "6.0.0-beta1"
  },
  "tagline" : "You Know, for Search"
}

Please let me know if this is enough information for you to be able to try to reproduce the issue. Thank you for the assistance!

spinscale · August 6, 2019, 9:31pm

This is not sufficient, as one cannot reproduce the exact operations. I copy pasted the example dates from your document, but the query works for me

PUT date_test
{
	"mappings": {
		"properties": {
					"ConnectionStatusLastChangeTime": {
						"type": "date",
						"format": "strict_date_time"
					}
		}
	}
}

POST date_test/_doc
{
  "ConnectionStatusLastChangeTime": "2019-02-11T17:49:12.617Z"
}

POST date_test/_doc
{
  "ConnectionStatusLastChangeTime": "2019-08-03T17:03:21.537Z"
}

POST date_test/_refresh

#returns one document
GET date_test/_search 
{
	"query": {
		"range": {
			"ConnectionStatusLastChangeTime": {
				"gte": "now-7d"
			}
		}
	}
}

does the above work for you as well? Can you show how you insert the document?

jgorlin · August 13, 2019, 2:58pm

Hi Alex,

Apologies for the delay, I was traveling last week. Below is the simple PHP script I'm using to import the data, using the official ES client for PHP. When I try this from scratch with a new index, with the script below, I get many dates that fall outside the 7 day window. Can you try it in your environment and see if you get the same result?

<?php
use Elasticsearch\ClientBuilder;
require 'vendor/autoload.php';
date_default_timezone_set('UTC');
$hosts = ['<insert ES host>'];
$client = Elasticsearch\ClientBuilder::create()           // Instantiate a new ClientBuilder
                    ->setHosts($hosts)      // Set the hosts
                    ->build();              // Build the client object
$dateArray = json_decode(file_get_contents("dateData.json"), true);
$elasticOutput = "";
$elasticRecordCount = 0;
$i = 0;
foreach($dateArray as $row){
    $params['body'][] = [
        'index' => [
            '_index' => 'date_test'
	]
    ];
    $params['body'][] = $row;
	// Every 1000 documents stop and send the bulk request
    if ($i % 1000 == 0) {
        $responses = $client->bulk($params);
		if (isset($responses['errors']) === true && $responses['errors'] === true) {
			$elasticOutput .= "There was one or more errors in the bulk response.".$responses['errors'];
		}else{
			$elasticRecordCount += sizeof($responses['items']);
		}
        // erase the old bulk request
        $params = ['body' => []];
        // unset the bulk response when you are done to save memory
        unset($responses);
    }
	$i += 1;
}
// Send the last batch if it exists
if (!empty($params['body'])) {
    $responses = $client->bulk($params);
	if (isset($responses['errors']) === true && $responses['errors'] === true) {
		$elasticOutput .= "There was one or more errors in the bulk response.".$responses['errors'];
	}else{
		$elasticRecordCount += sizeof($responses['items']);
	}
}
echo ", Elastic Records Indexed: $elasticRecordCount (errors: $elasticOutput)";
?>

spinscale · August 13, 2019, 3:15pm

I'm sorry, but I do not run PHP on my local system. Using a curl/dev-tools example is the only way to reproduce it for me.

jgorlin · August 13, 2019, 3:28pm

Is there a paid support option on a call by call basis? I feel like we aren't making progress here and I'm using all the standard and supported tools to do this. Any other ideas on how I can trace down this issue?

jgorlin · August 13, 2019, 3:46pm

Ok, I've gone ahead and taken a tcpdump to get the raw HTTP bulk request and still seeing the same behavior.

Please use the following:

Create Index:

PUT date_test_import
{
	"mappings": {
		"properties": {
					"ConnectionStatusLastChangeTime": {
						"type": "date",
						"format": "strict_date_time"
					}
		}
	}
}

Run this bulk insert: bulkPost.txt

Search Results (every single result in the top 10 are incorrect):

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 1001,
      "relation" : "eq"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "rYpCqWEBQgB2YO0V1USO",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-03T17:04:11.298Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "1YpDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-02T14:29:54.42Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "1opDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-02T02:31:46.726Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "14pDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-02-11T17:49:12.617Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "2IpDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-03T17:03:21.537Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "2YpDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-05T18:08:32.589Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "2opDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-06T03:46:32.062Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "24pDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2018-08-16T20:21:46.974Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "3IpDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-06T04:49:58.553Z"
        }
      },
      {
        "_index" : "date_test_import",
        "_type" : "_doc",
        "_id" : "3YpDqWEBQgB2YO0V9EUH",
        "_score" : 1.0,
        "_source" : {
          "ConnectionStatusLastChangeTime" : "2019-08-02T05:22:42.549Z"
        }
      }
    ]
  }
}

jgorlin · August 13, 2019, 3:53pm

Wow, just realized my problem. The time on my ES server is way off, thinks it is Feb 2018! I'm so sorry for missing something so obvious. After making this change, the search result is coming back as empty.

Thanks for your help on this, it is much appreciated!

system · September 10, 2019, 3:53pm

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