PostgreSQL查询：合并startDate与startTime字段生成新列

with data as(
    SELECT 
        c."id",
        c."accountId",
        c."name",
        c."campaignType",
        c."status",
        -- 合并日期与时间为单个StartTime字段
        CASE
            -- 当存在initiatedAt时，直接使用该时间戳
            WHEN cb."executionDetails"->>'initiatedAt' IS NOT NULL THEN cast(cb."executionDetails"->>'initiatedAt' as TIMESTAMP)
            -- 无initiatedAt时，拼接startDate和计算出的startTime
            ELSE (csr."startDate"::TIMESTAMP + (
                CASE 
                    WHEN csr."timeSlot"->>'type'='MORNING' THEN '07:00'::TIME
                    WHEN csr."timeSlot"->>'type'='AFTERNOON' THEN '12:00'::TIME
                    WHEN csr."timeSlot"->>'type'='EVENING' THEN '17:00'::TIME
                    WHEN csr."timeSlot"->>'type'='CUSTOM' THEN ((csr."timeSlot"->>'startTime')::json->>'hour'||':'||(csr."timeSlot"->>'startTime')::json->>'minute')::TIME
                    ELSE (csr."timeSlot"->>'startTime')::TIME
                END
            ))
        END AS "StartTime",
        CASE 
            WHEN cb."executionDetails"->>'initiatedAt' IS NOT NULL THEN NULL 
            ELSE csr."timeSlot"->>'type' 
        END as "timeSlotType",
        split_part(cb."batchRunId", '-',6)::decimal as batchNumber,  
        'CAMPAIGN' as type
    FROM "Campaigns" c 
    LEFT JOIN "CampaignScheduleRequests" csr ON c."id"=csr."campaignId" 
    LEFT JOIN "CampaignBatches" cb ON csr."id"=cb."requestId"
) 
SELECT * FROM data as d
WHERE d."status" IN ('ACTIVATED') 

• 移除了原查询中的startDate和startTime字段，替换为合并后的StartTime字段，类型为TIMESTAMP
• 逻辑拆分两种场景：
  1. 当cb."executionDetails"中存在initiatedAt值时，直接将其转为时间戳作为StartTime
  2. 无initiatedAt值时，把csr."startDate"转为时间戳后，加上计算得到的时段时间（或自定义时间），完成日期与时间的拼接
• 保留了原查询中其他所有字段和过滤条件，确保业务逻辑不变

内容的提问来源于stack exchange，提问作者P J