SqlAlchemy temporal operations not properly handled #33
Description
Correct me if i'm completely misusing this but I'm trying to perform a simple "meets" expression as cql-json:
For simplicity my db contains the following datetimes:
1: 2019-06-24T07:41:10Z
2: 2019-06-24T07:41:13Z
3: 2019-06-24T07:41:17Z
4: 2019-06-24T07:41:21Z
And I want to perform a "meets" request with time interval 2019-06-24T07:41:13 - 2019-06-24T07:41:18 as input.
This should result in all rows from the db where the datetime coinsides with the beginning of the interval, which in my db is only the second row: 2019-06-24T07:41:13Z
However running the query, it seems to parse correctly:
from pygeofilter.parsers.cql_json import parse as parse_json
from pygeofilter.backends.sqlalchemy import to_filter
import sqlalchemy as sa
cql_json = "meets": [{ "property": "datetime" }, ["2019-06-24T07:41:13Z", "2019-06-24T07:41:18Z"]]
ast = parse_json(cql_json)
TimeMeets(lhs=ATTRIBUTE datetime, rhs=Interval(start=datetime.datetime(2019, 6, 24, 7, 41, 13, tzinfo=<StaticTzInfo 'Z'>), end=datetime.datetime(2019, 6, 24, 7, 41, 18, tzinfo=<StaticTzInfo 'Z'>)))
but when it evaluates the ast to a SqlAlchemy expression, and queries the db, it returns both row 2 and 3: 2019-06-24T07:41:13Z and 2019-06-24T07:41:17Z
Inspecting the temporal method of filters.py in the sqlalchemy backend:
it seems to handle special cases for before, after and tequals operations, thus other temporals end up being evaluated as either between, >=, or <= so my query ends up being evaluated as a between(datetime, "2019-06-24T07:41:13Z", "2019-06-24T07:41:18Z") explaining why row 3 is also returned
On a sidenote:
It seems that you have to specify an interval as input for anything besides before and after otherwise this crashes:
with TypeError: cannot unpack non-iterable datetime.datetime object
From the OAF specification:
CQL supports date and timestamps as time instants, but even the smallest "instant" has a duration and can also be evaluated as an interval.
Hence to my understanding you should be able to run "meets", "metby", "begins" ect. on exact timedates, treating them as intervals, and not having to explicitly specify an interval (despite it being nonsensical to do in practise)
Am I completely missing something here or is the temporal method in fact not handling all temporal cases?