Handling start_datetime and end_datetime?

[lossyrob]

There's some datasets that do not have a datetime, but instead have a start_datetime and end_datetime. For example, a landcover dataset that represents a year of data - there's no single datetime to represent the data. STAC specifies that in this case datetime is null and the start_datetime and end_datetime is set.

How can pgstac handle these types of items?

[bitner]

We can create a composite that stores/indexes the datetime as a tstzrange datatype that is set to an instant (range start = range end) and use postgresql inbuilt range operators (similar to postgis bbox operators) to query between the stac record time range and the passed in start/end times.

We could also keep the start/end of the stac record as separate start/end where we merge in the datetime such that start_datetime = coalesce(datetime, start_datetime) and end_datetime = coalesce(datetime, end_datetime) and use appropriate bilateral less than and greater than statements.

I think the biggest question is do we always want to use some sort of "merged" time to query against or only with a flag or in certain circumstances.

[paudley]

The tztsrange solution would certainly be more flexible for queries.