Querying on last_modified not working.

[JPBergsma]

I have noticed that querying on the last_modified field is not very reliable.

I performed the query localhost:5000/v1/structures?filer=last_modified>=2019-07-09T05:14:38Z
The result contains the entry "mpf_1" which has last_modified = 2019-06-08T03:13:37Z which is clearly outside the requested data range.
I used optimade python tools version 0.16.10

[ml-evs]

Yep, something is broken here.

You can try flipping the comparison at the link below and you'll see the same IDs turning up.

https://optimade.herokuapp.com/v1/structures?filer=last_modified<2019-07-09T05:14:38Z&response_fields=last_modified

We definitely have a test for this:

optimade-python-tools/tests/filtertransformers/test_mongo.py

Lines 522 to 568 in bb3c5a1

	def test_suspected_timestamp_fields(self, mapper):
	import datetime
	import bson.tz_util
	from optimade.filtertransformers.mongo import MongoTransformer
	from optimade.server.warnings import TimestampNotRFCCompliant
	example_RFC3339_date = "2019-06-08T04:13:37Z"
	example_RFC3339_date_2 = "2019-06-08T04:13:37"
	example_non_RFC3339_date = "2019-06-08T04:13:37.123Z"
	expected_datetime = datetime.datetime(
	year=2019,
	month=6,
	day=8,
	hour=4,
	minute=13,
	second=37,
	microsecond=0,
	tzinfo=bson.tz_util.utc,
	)
	assert self.transform(f'last_modified > "{example_RFC3339_date}"') == {
	"last_modified": {"$gt": expected_datetime}
	}
	assert self.transform(f'last_modified > "{example_RFC3339_date_2}"') == {
	"last_modified": {"$gt": expected_datetime}
	}
	non_rfc_datetime = expected_datetime.replace(microsecond=123000)
	with pytest.warns(TimestampNotRFCCompliant):
	assert self.transform(f'last_modified > "{example_non_RFC3339_date}"') == {
	"last_modified": {"$gt": non_rfc_datetime}
	}
	class MyMapper(mapper("StructureMapper")):
	ALIASES = (("last_modified", "ctime"),)
	transformer = MongoTransformer(mapper=MyMapper)
	parser = LarkParser(version=self.version, variant=self.variant)
	assert transformer.transform(
	parser.parse(f'last_modified > "{example_RFC3339_date}"')
	) == {"ctime": {"$gt": expected_datetime}}
	assert transformer.transform(
	parser.parse(f'last_modified > "{example_RFC3339_date_2}"')
	) == {"ctime": {"$gt": expected_datetime}}

Looks fine to me, so I guess the issue is either in the parsing (i.e. does it get to the timestamp detection stage) or in Mongo itself (i.e. the query we are generating above does not do what we think it does).

We should probably check if it works for elastic too.

[JPBergsma]

I think I made a typo in the link I wrote "filer" instead of "filter".
This probably caused the wrong data to be returned.
I also should have put the data between quotes, otherwise the lark parser can't handle the date.
This should be a proper query for handling dates:
https://optimade.herokuapp.com/v1/structures?filter=last_modified%3C%222019-06-08T04:13:37Z%22

[ml-evs]

Haha phew, I had tried to replicate this in the past and struggled. I must have made the same typo a few times myself 😅