PostgreSQL中TO_CHAR处理interval周格式为何与日/月不同？

我尝试使用TO_CHAR()函数将interval值转换为字符串，但发现当使用周格式符WW时，返回值至少为'01'，而日格式符DD、月格式符MM则会正常返回'00'。测试示例如下：

postgres=# select to_char('2023-07-02'::timestamp - '2023-07-02'::timestamp, 'DD') as period;
 period
--------
 00
(1 row)

postgres=# select to_char('2023-07-02'::timestamp - '2023-07-02'::timestamp, 'WW') as period;
 period
--------
 01
(1 row)

postgres=# select to_char('2023-07-02'::timestamp - '2023-07-02'::timestamp, 'MM') as period;
 period
--------
 00
(1 row)

甚至直接传入interval类型值测试，结果一致：

postgres=# select to_char(interval '0 days', 'WW') as period;
 period
--------
 01
(1 row)

postgres=# select to_char(interval '0 days', 'DD') as period;
 period
--------
 00
(1 row)

postgres=# select to_char(interval '0 days', 'MM') as period;
 period
--------
 00
(1 row)

问题原因 #

这是因为PostgreSQL的TO_CHAR()函数处理interval的WW格式时，是基于年份中的周数逻辑，即使间隔为0，也会默认返回当前日期所在的周数，而非间隔对应的周数。而DD、MM是直接提取间隔中的日、月数值，0间隔自然返回00。

可靠的转换方法 #

要准确获取interval对应的周数字符串，需要手动计算间隔的总时长，转换为周数后再格式化输出：

方法1：基于天数计算周数 #

先提取间隔的总天数，除以7得到周数，再用TO_CHAR()格式化为两位字符串：

-- 0间隔场景
SELECT TO_CHAR(FLOOR(EXTRACT(DAY FROM interval '0 days') / 7)::INTEGER, 'FM00') AS period;
-- 输出：00

-- 10天间隔场景
SELECT TO_CHAR(FLOOR(EXTRACT(DAY FROM interval '10 days') / 7)::INTEGER, 'FM00') AS period;
-- 输出：01

-- 2个月间隔场景（按实际天数计算）
SELECT TO_CHAR(FLOOR(EXTRACT(DAY FROM interval '2 months') / 7)::INTEGER, 'FM00') AS period;

方法2：基于秒数精确计算 #

如果需要更精确的周数（考虑小时、分钟等细分时长），可以通过总秒数转换：

-- 计算间隔对应的精确周数
SELECT TO_CHAR(FLOOR(EXTRACT(EPOCH FROM interval '1 week 2 days') / (7*24*3600))::INTEGER, 'FM00') AS period;
-- 输出：01

这里7*24*3600是一周的总秒数，通过EXTRACT(EPOCH FROM ...)获取间隔的总秒数后，相除得到精准周数。

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