YYYYMMDD日期处理：提取月份补零与SQL查询语句简化问询

1. 实现月份两位数字显示 #

EXTRACT(month ...)返回的是数值类型，自然不会保留前导零。换用TO_CHAR函数，将转换后的日期按MM格式输出即可得到两位带前导零的月份：

TO_CHAR(to_date(block_date::text, 'yyyymmdd'), 'MM')

2. 简化查询语句 #

你当前的查询重复执行了两次to_date(block_date::text, 'yyyymmdd')，完全可以只转换一次，再复用结果。这里提供几种简化方案：

方案一：利用CTE复用转换后的日期 #

WITH converted AS (
    SELECT to_date(block_date::text, 'yyyymmdd') AS block_dt
    FROM test
)
SELECT
    CONCAT(
        TO_CHAR(CURRENT_DATE, 'DD.MM.YY'),
        '_',
        TO_CHAR(block_dt, 'YYYY'),
        '_',
        TO_CHAR(block_dt, 'MM')
    ) AS result
FROM converted;

方案二：直接在子查询中处理日期转换 #

SELECT
    CONCAT(
        TO_CHAR(CURRENT_DATE, 'DD.MM.YY'),
        '_',
        TO_CHAR(block_dt, 'YYYY'),
        '_',
        TO_CHAR(block_dt, 'MM')
    ) AS result
FROM (
    SELECT to_date(block_date::text, 'yyyymmdd') AS block_dt
    FROM test
) t;

方案三：更简洁的字符串拼接（适用于PostgreSQL） #

PostgreSQL支持用||做字符串连接，同时可以直接用YYYY_MM格式符把年份和月份一次性格式化为2023_01，省去两次TO_CHAR调用：

SELECT
    TO_CHAR(CURRENT_DATE, 'DD.MM.YY') || '_' || TO_CHAR(to_date(block_date::text, 'yyyymmdd'), 'YYYY_MM') AS result
FROM test;

这个写法既解决了月份补零问题，又最大限度简化了语句，避免重复的日期转换操作。

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