PostgreSQL中日期时间列格式化为指定格式并转文本的实现

核心实现逻辑 #

PostgreSQL 中处理日期类值转文本场景，优先使用原生to_char()函数完成格式化，而非直接对DATE类型做隐式CAST转文本——后者的输出格式受数据库DateStyle本地化参数影响，格式不可控，非常容易出现匹配失效的问题。to_char()函数返回值本身就是text类型，不需要额外做类型转换。

对应两个业务场景的写法 #

• 场景1：将日期值转为DD/MM/YYYY格式的文本
  直接在to_char()的格式参数中指定模板即可，写法为：to_char(日期列值, 'DD/MM/YYYY')。
  示例：如果需要把draft表的created_on列转为DD/MM/YYYY格式文本输出，直接写to_char(dt.created_on::date, 'DD/MM/YYYY') AS created_on_text即可。
• 场景2：draft表created_on列格式化后做LIKE匹配查询
  原有参考SQL存在几处可修正的问题：
  1. 存在语法错误：dt.user_id,,位置多了一个逗号，会直接导致执行失败
  2. 原写法CAST(DATE(dt.created_on) AS TEXT)格式不可靠：如果数据库的DateStyle参数被修改为非ISO格式，CAST输出的日期字符串格式就会变化，直接导致LIKE匹配失效
  3. 原写法中LOWER()函数完全冗余：YYYY-MM-DD格式的字符串没有字母，转小写操作不会产生任何实际效果，还会增加不必要的计算开销
  4. 如果仅需要匹配某一整天的记录（比如2020-10-30当天的所有数据），不需要转文本做LIKE，直接用日期范围判断即可，这类写法可以直接命中created_on列上的B-tree索引，查询性能远高于全表扫描的文本匹配。

  修正后的通用查询SQL #

  select
      dt.client_id,
      dt.user_id,
      to_char(dt.created_on::date, 'YYYY-MM-DD') as created_on,
      dt.created_by,
      dt.last_update_on,
      dt.last_update_by
  from
      draft dt
  where
      dt.user_id = 'ABC'
      -- 格式完全可控，支持任意文本片段模糊匹配
      and to_char(dt.created_on::date, 'YYYY-MM-DD') LIKE '%2020-10-30%'
  

  高性能替代写法（精准匹配单天记录时使用） #

  如果业务逻辑是筛选created_on等于指定日期的所有记录，替换为范围判断的写法，避免对列套函数导致索引失效：

  select
      dt.client_id,
      dt.user_id,
      to_char(dt.created_on::date, 'YYYY-MM-DD') as created_on,
      dt.created_by,
      dt.last_update_on,
      dt.last_update_by
  from
      draft dt
  where
      dt.user_id = 'ABC'
      -- 匹配2020-10-30 00:00:00 到 2020-10-31 00:00:00 之间的所有记录，可直接命中列上的普通索引
      and dt.created_on >= '2020-10-30'::timestamp
      and dt.created_on < ('2020-10-30'::date + 1)::timestamp
  

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