PostgreSQL日期序数词格式化及b_cycle_type数值映射筛选问题

PostgreSQL的to_char()函数可以直接生成带序数词的日期格式，核心是使用th格式符，搭配FM修饰符能去除前导空格，让结果更贴合日常写法：

• 格式化完整日期（带序数词）：

  SELECT to_char(date '2024-05-01', 'FMDDth Month YYYY');
  -- 返回结果：'1st May 2024'
  

• 仅提取日的序数词：

  SELECT to_char(date '2024-05-27', 'FMDDth');
  -- 返回结果：'27th'
  

FM修饰符的作用是消除格式输出中的填充空格，避免出现' 1st'这类带前导空格的无效结果。

你的语句无返回结果，问题出在to_char(customers.b_cycle_type, '99th')的输出上：99格式符会为不足两位的数字补前导空格，比如数值27会被格式化为' 27th'，拼接后得到' 27th Day Of The Month'，和你要匹配的'27th Day Of The Month'（无前导空格）无法对齐。

解决方法是给to_char添加FM修饰符去除前导空格，修正后的语句如下：

SELECT "customers".* 
FROM   "customers" 
WHERE (CASE customers.b_cycle_type
         WHEN -1 THEN 'Last day of the month'
         WHEN  0 THEN 'Align with first'
         ELSE to_char(customers.b_cycle_type, 'FM99th') || ' Day Of The Month'
       END = '27th Day Of The Month')

如果b_cycle_type的数值都是1-31这类合法日期天数，也可以简化逻辑为数值匹配，性能更优（避免每条记录执行字符串拼接和匹配）：

SELECT "customers".* 
FROM   "customers" 
WHERE (
  (customers.b_cycle_type = -1 AND 'Last day of the month' = '27th Day Of The Month')
  OR (customers.b_cycle_type = 0 AND 'Align with first' = '27th Day Of The Month')
  OR (customers.b_cycle_type BETWEEN 1 AND 31 AND to_char(customers.b_cycle_type, 'FM99th') || ' Day Of The Month' = '27th Day Of The Month')
);

若筛选条件是动态传入的，第一种CASE写法更易维护。

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