Adding a queryable for time queries

[mirober]

I'm looking to implement queries based on time as well as datetime. I did some poking around and found the queryables table, and it looks like I should be able to do something like:

insert into queryables
(name, property_path, property_wrapper)
values
('time', '((content->''properties''->>''datetime'')::timestamptz)::time', 'time');

And then search by doing:

select search('{"limit": 1, "query": {"time": {"gte": "1:10", "lte": "1:50"}}}'::jsonb);

This works in the tests that I've done. It won't return records that don't have a single datetime property set but that is fine.

A couple of questions. Firstly, is this a reasonable thing to do? I'd ideally like to avoid adding redundant metadata to every item if it can be cheaply calculated during the search. Is there anything else I should do to make this work well beyond just inserting into the queryables table?

Secondly, one issue I can see is that the index creation query generated by https://github.com/stac-utils/pgstac/blob/main/src/pgstac/sql/002a_queryables.sql#L192 doesn't know that time is an expression and not just a normal property. It generates something like CREATE INDEX ON %I USING BTREE (content->'properties'::text->'time'::text) which won't help. The simplest way I can see to fix this would be to add a index_query column to the queryables table that allows you to specify a custom query, or maybe a index_expression column that would override the default content->'properties'::text->'time'::text. This could also potentially replace the if/elses in indexdef for id, datetime and geometry. Would you accept a PR for this?

Cheers, Mike

[mirober]

Ah, interesting, it looks like I can just do:

INSERT INTO queryables (name, property_path, property_wrapper) VALUES ('time', 'datetime', 'time');

since datetime is already stored as a column as well as in the content properties. Table scan on a timestamp column should be fast enough without an index