Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Syntax error from temporal_op_query #281

Open
captaincoordinates opened this issue Jun 23, 2024 · 1 comment
Open

Syntax error from temporal_op_query #281

captaincoordinates opened this issue Jun 23, 2024 · 1 comment

Comments

@captaincoordinates
Copy link

Given what I believe to be a valid temporal filter, pgstac's temporal_op_query function appears to generate invalid SQL which raises a syntax error. This may simply be a case of me misunderstanding how to execute the function, so any additional information would be very much appreciated. I'm really struggling to find comprehensive documentation on temporal filters.

The following query's temporal operator is translated to an SQL construct here. However it appears that AND rl < lh < rh is invalid SQL, and should in fact be AND rl < lh AND lh < rh.

SELECT search('{
    "filter": {
        "op": "t_overlaps",
        "args": [
            "2011-08-16T00:00:00Z/2011-08-17T00:00:00Z"
        ]
    }
}')
ERROR:  syntax error at or near "<"
LINE 1: ...6 00:00:00+00'::timestamptz AND rl < end_datetime < '2011-08...
QUERY:  EXPLAIN (format json) SELECT 1 FROM items WHERE (datetime < '2011-08-16 00:00:00+00'::timestamptz AND rl < end_datetime < '2011-08-17 00:00:00+00'::timestamptz);

Because of challenges around documentation I'm not sure if this is the right way to perform t_overlaps. The extension documentation only provides an example of an intersection and from what I can gather pgstac's unit tests only address t_intersects, t_after, and t_before operators.

@captaincoordinates
Copy link
Author

I'm also interested in learning more about the reasoning around temporal operator SQL statements. From what I can tell pgstac disregards any property provided in the JSON filter and always targets datetime and end_datetime

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant