collection_temporal_extent produces invalid datetime

[jonas-eberle]

The function collection_temporal_extent (e.g., used in update_collection_extents) produces datetimes invalid to the STAC specification:

pgstac/src/pgstac/sql/003a_items.sql

Lines 337 to 341 in 0c88741

	CREATE OR REPLACE FUNCTION collection_temporal_extent(id text) RETURNS jsonb AS $$
	SELECT to_jsonb(array[array[min(datetime)::text, max(datetime)::text]])
	FROM items WHERE collection=$1;
	;
	$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE SET SEARCH_PATH TO pgstac, public;

The current function results the following temporal interval:

 [["2002-07-01 00:00:00+00", "2024-03-01 00:00:00+00"]]

However, the STAC Collection json schema requires the following pattern: (\\+00:00|Z)$
https://github.com/radiantearth/stac-spec/blob/master/collection-spec/json-schema/collection.json#L169

A valid timestamp in STAC should be like this:

[["2000-02-01T00:00:00+00:00", "2024-03-01T00:00:00+00:00"]]

In my case it works to just remove the timestamp conversion to text (::text) from the function above.

[hrodmn]

We have at least two options that would get extents that are compliant with the STAC spec:

1. Verbosely specify the format and convert to UTC (with Z suffix):

SELECT to_jsonb(array[array[
    to_char(min(datetime) AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
    to_char(max(datetime) AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
]])
FROM items WHERE collection='pgstac-test-collection'
;

                      to_jsonb
════════════════════════════════════════════════════
 [["2011-07-31T00:00:00Z", "2011-08-25T00:00:00Z"]]
(1 row)

2. As @jonas-eberle suggested, we can also let to_jsonb handle the formatting which gives us the offset in HH:MI (instead of just HH):

select to_jsonb(array[array[min(datetime), max(datetime)]])
from items where collection='pgstac-test-collection';

                           to_jsonb
══════════════════════════════════════════════════════════════
 [["2011-07-31T00:00:00+00:00", "2011-08-25T00:00:00+00:00"]]
(1 row)

@bitner what do you think?

[bitner]

yeah, if the to_jsonb casting is giving us the expected format, let's just use that.

[hrodmn]

collection_extent is the function that populates the extent field in the collection json, and it produces well-formatted timestamps, but collection_temporal_extent does not produce well-formatted timestamps:

select jsonb_pretty(collection_extent('pgstac-test-collection'));
                 jsonb_pretty
══════════════════════════════════════════════
 {                                           ↵
     "spatial": {                            ↵
         "bbox": [                           ↵
             [                               ↵
                 -88.00382995605469,         ↵
                 30.496826171875,            ↵
                 -85.24566650390625,         ↵
                 31.003585815429688          ↵
             ]                               ↵
         ]                                   ↵
     },                                      ↵
     "temporal": {                           ↵
         "interval": [                       ↵
             [                               ↵
                 "2011-07-31T00:00:00+00:00",↵
                 "2011-08-25T00:00:00+00:00" ↵
             ]                               ↵
         ]                                   ↵
     }                                       ↵
 }
(1 row)

select jsonb_pretty(collection_temporal_extent('pgstac-test-collection'));
           jsonb_pretty
═══════════════════════════════════
 [                                ↵
     [                            ↵
         "2011-07-31 00:00:00+00",↵
         "2011-08-25 00:00:00+00" ↵
     ]                            ↵
 ]
(1 row)

I don't think the issue here has much (if any) impact, but we can easily make sure that collection_temporal_extent produces consistent results.