时间戳

首页 > 解决方案 > 如何使用列表 {11:00:00,12:00:00} 选择 TO_CHAR

sql - 如何使用列表 {11:00:00,12:00:00} 选择 TO_CHAR

问题描述

我有这张桌子：

postgres::DATABASE=> SELECT * FROM db;
       timeList     |   time   
---------+------------+---------------------
{11:00:00,12:00:00} | 11:22:33
{19:00:00}          | 12:12:33

我需要在timeList几秒钟内选择值。但收到此错误：

postgres::DATABASE=> SELECT TO_CHAR(timeList, 'HH24:MI') timeList FROM db;

ERROR:  syntax error at or near "timeList"
LINE 1: SELECT TO_CHAR(time, 'HH24:MI') timeList FROM db;
                                        ^

选择单个值的命令按预期工作：

postgres::DATABASE=> SELECT TO_CHAR(time, 'HH24:MI') time FROM db;
 time  
-------
 11:22
 12:12

标签： sqlarrayspostgresqltypesdatetime-format

解决方案

又快又脏

假设列"timeList"是 type time[]，有一种快速而肮脏的方式。强制转换为varchar(5)[]截断时间的字符串表示，以便仅保留 HH24:MI。

test=> SELECT '{12:12, 1:1:1.123456, 23:59:59.999999}'::time[]::varchar(5)[];
       varchar       
---------------------
 {12:12,01:01,23:59}
(1 row)

db<>在这里摆弄

它甚至可以开箱即用地表示 NULL 和空数组。“quick'n'dirty”相当干净。

当然，这取决于time值的文本表示的实现细节。但它适用于我知道的任何可能的设置，而且我认为它不会很快改变。有关的：

缓慢而确定

要以您想要的任何方式格式化输出，Laurenz 提供的总是缓慢而可靠的方式：unnest、格式化、聚合回来。LATERAL但是在子查询中使用 ARRAY 构造函数立即聚合回来通常更快。这也保证保持数组元素的原始顺序，而不用跟踪WITH ORDINALITY：

SELECT *
FROM   db, LATERAL (
   SELECT ARRAY(SELECT to_char(unnest(db.timelist), 'HH24:MI'))
   ) x(times_formatted);

另外，子查询的聚合总是产生一行，所以它不会{}在主表中默默地消除带有 NULL 或空数组 ( ) 的行（就像 Laurenz 的查询一样）。

隐式CROSS JOIN仍然有一个副作用：它转换NULL为一个空数组 ( {})。
可以通过以下方式正确避免LEFT JOIN：

SELECT *
FROM   db
LEFT   JOIN LATERAL (
   SELECT ARRAY(SELECT to_char(unnest(db.timelist), 'HH24:MI'))
   ) x(times_formatted) ON db.timelist IS NOT NULL;

考虑小提琴中的演示。

db<>在这里摆弄

有关的：

推荐阅读