PostgreSQL如何查询带时区时间戳数组列存储的最新时间值

需要从Login表的last_updated_date字段中提取存储的最新带时区时间戳。该字段数据类型为timestamp with time zone[]（带时区时间戳数组），示例存储值下预期返回结果为2022-06-11 05:13:10.559+00。

表结构 #

Table: Login
---------------------------------------
Id
username
login_attempt
last_updated_date timestamp with time zone[]

字段存储示例 #

last_updated_date字段的典型存储内容格式如下：

{"2022-01-12 12:14:50.329+00","2022-02-17 03:49:45.525+00","2022-06-11 05:13:10.559+00"}

PostgreSQL 中可以通过unnest()函数将数组元素拆分为独立行，再用聚合函数MAX()直接计算时间最大值，不需要额外做类型转换，数据库原生支持timestamptz类型的大小比较。

• 查询单条指定记录的最新时间：

SELECT MAX(ts) AS latest_update_time
FROM unnest(
  -- 替换为实际的记录筛选条件
  (SELECT last_updated_date FROM Login WHERE Id = 1)
) AS ts;

• 批量查询表中所有记录各自对应的最新更新时间：

SELECT
  l.Id,
  l.username,
  l.login_attempt,
  calc.latest_ts
FROM Login l,
LATERAL (
  SELECT MAX(ts_val) AS latest_ts
  FROM unnest(l.last_updated_date) AS ts_val
) calc;

以上语句针对给出的示例数据执行后，会返回预期的2022-06-11 05:13:10.559+00结果。

内容的提问来源于stack exchange，提问作者Dr. Rahul Jha