转换PostgreSQL中的时间戳数组(不在表中)

-- 创建一个包含时间戳字符串的数组
WITH timestamp_strings AS (
    SELECT ARRAY['2023-01-01 12:00:00', '2023-01-02 12:00:00', '2023-01-03 12:00:00'] AS timestamps
)

-- 将字符串数组转换为时间戳数组
SELECT ARRAY[
    TO_TIMESTAMP(timestamps[1], 'YYYY-MM-DD HH24:MI:SS'),
    TO_TIMESTAMP(timestamps[2], 'YYYY-MM-DD HH24:MI:SS'),
    TO_TIMESTAMP(timestamps[3], 'YYYY-MM-DD HH24:MI:SS')
] AS timestamp_array
FROM timestamp_strings;

SELECT TO_TIMESTAMP('2023-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- 正确
SELECT TO_TIMESTAMP('2023/01/01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'); -- 错误

WITH timestamp_strings AS (
    SELECT ARRAY[]::text[] AS timestamps -- 空数组
)
SELECT CASE WHEN array_length(timestamps, 1) > 0 THEN
    ARRAY[
        TO_TIMESTAMP(timestamps[1], 'YYYY-MM-DD HH24:MI:SS'),
        TO_TIMESTAMP(timestamps[2], 'YYYY-MM-DD HH24:MI:SS'),
        TO_TIMESTAMP(timestamps[3], 'YYYY-MM-DD HH24:MI:SS')
    ]
ELSE
    ARRAY[]::timestamp[]
END AS timestamp_array
FROM timestamp_strings;