ST_attrTimestampFilter-PolarDB(PolarDB)-阿里云帮助中心

Filters trajectory points by a timestamp attribute field and returns an array of the matching attribute values.

Syntax

-- Single-value filter
timestamp[] ST_attrTimestampFilter(trajectory traj, cstring attr_field_name, cstring operator, timestamp value);

-- Range filter
timestamp[] ST_attrTimestampFilter(trajectory traj, cstring attr_field_name, cstring operator, timestamp value1, timestamp value2);

Parameters

Parameter	Type	Description
`traj`	trajectory	The trajectory object.
`attr_field_name`	cstring	The name of the attribute field to filter on.
`operator`	cstring	The filter operator. Valid values: `=`, `!=`, `>`, `<`, `>=`, `<=`, `[]`, `(]`, `[)`, `()`.
`value` / `value1`	timestamp	The filter value (single-value filter), or the lower bound of the range (range filter).
`value2`	timestamp	The upper bound of the range. Required only for the range filter overload.

Examples

The following example creates a trajectory with a heading attribute of type timestamp, then retrieves the heading values that are greater than 2010-01-01 15:00:00.

INSERT INTO traj VALUES (
  3,
  ST_makeTrajectory(
    'STPOINT'::leaftype,
    st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326),
    ARRAY['2010-01-01 14:30'::timestamp, '2010-01-01 15:00', '2010-01-01 15:30'],
    '{"leafcount": 3, "attributes": {"heading": {"type": "timestamp", "nullable": false, "value": ["Fri Jan 01 14:30:00 2010", "Fri Jan 01 15:00:00 2010", "Fri Jan 01 15:30:00 2010"]}}}'
  )
);

SELECT st_attrTimestampFilter(traj, 'heading', '>', 'Fri Jan 01 15:00:00 2010'::timestamp)
FROM traj
WHERE id = 3;

Output:

 st_attrtimestampfilter
-------------------------
 {"2010-01-01 15:30:00"}
(1 row)