PostgreSQL中基于计算结果使用interval语法的实现方法

作为PostgreSQL新手，处理日期与数据类型时碰到了Interval的动态使用问题：固定时长的Interval用法（比如select '2024-01-12'::date + interval '1 day' NextDay;）能正常运行，但当Interval时长来自计算结果时，多次尝试都报语法错误。最终通过将计算结果转为秒数（乘以86400，即一天的秒数），再转成文本和Interval类型的方式实现了需求，示例代码可获取当年1月1日及当年第一个周一的日期。

错误尝试示例 #

以下几种写法均触发SQL Error [42601]: ERROR: syntax error：

select 
      '2024-01-12'::date + interval ((8 - extract(isodow from date_trunc('year', now())::text || ' day') FirstMondayDate___GivesError_1 -- 错误：语法错误在"("附近
    , '2024-01-12'::date + interval (8 - extract(isodow from date_trunc('year', now())::text || ' day' FirstMondayDate___GivesError_2 -- 错误：语法错误在"-"附近
    , '2024-01-12'::date + interval '8 day' - interval extract(isodow from date_trunc('year', now())::text || ' day' FirstMondayDate___GivesError_3 -- 错误：语法错误在"("附近
    , '2024-01-12'::date + interval '8 day' - interval (extract(isodow from date_trunc('year', now()))  FirstMondayDate___GivesError_4 -- 错误：语法错误在"extract"附近
;

正确实现代码 #

select 
    date_trunc('year', now())::date DateFirstJanThisYear
    , case 
        when extract(isodow from date_trunc('year', now())::date) = 1 then date_trunc('year', now())
        when extract(isodow from date_trunc('year', now())::date) > 1 then date_trunc('year', now())::date + ((8 - extract(isodow from date_trunc('year', now())::date))*86400)::text::interval
    end::date DateFirstMondayThisYear
;

原理说明 #

PostgreSQL中interval关键字后要求直接跟字符串字面量，无法直接使用表达式生成时长。通过将计算出的天数乘以86400转成秒数，再转换为文本类型，最后转为interval，就能动态生成所需的时间间隔，避免语法错误。

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