PostgreSQL中如何按JS格式日期字符串实现正确排序查询

当前直接对date字段排序结果不准的核心原因是：该字段存储的是纯文本字符串，ORDER BY date会按照字典序逐字符比对排序，而非真实的时间先后顺序。数据量少的时候，同一年、同一月份的字符串字典序碰巧和时间顺序一致，结果看似正常；一旦跨月份、跨时区的日期记录混杂，字典序和真实时间序的偏差会直接暴露，排序结果就会失效。

方案1：零表结构改动，直接调整查询逻辑 #

适合中小数据量的场景，所有原有读取date字段做字符串解析的业务代码完全不受影响。
核心是在排序环节，用PostgreSQL内置的to_timestamp函数，把存储的JS格式日期字符串实时转换为带时区的标准时间戳，再按转换后的时间值排序。
针对你给出的日期格式Tue Dec 29 2020 23:16:54 GMT-0800 (Pacific Standard Time)，匹配的格式串不需要额外处理末尾括号里的时区全称文本（to_timestamp会自动忽略未匹配的尾部内容），只需要对齐前面的固定结构即可。

修改后的完整Node.js查询代码如下：

pool.query(
  "SELECT * FROM table WHERE field1 = $1 ORDER BY to_timestamp(date, 'Dy Mon DD YYYY HH24:MI:SS GMT TZ') DESC LIMIT 10",
  [val1],
  (err, table) => {
    if(err) {
      return res.status(400)
      done()
    } else {
      res.json(table.rows)
    }
  }
)

其中格式串的对应匹配关系：

• Dy 匹配字符串开头的三位星期缩写（如Tue、Mon）
• Mon 匹配三位月份缩写（如Dec、Jan）
• DD 匹配两位日期数字
• YYYY 匹配四位年份
• HH24:MI:SS 匹配24小时制的时分秒部分
• GMT TZ 匹配GMT标识与对应的时区偏移量（如GMT-0800）

方案2：大数据量场景的性能优化 #

如果单表数据量达到百万级以上，每次查询都实时转换字符串会产生不必要的性能损耗，可以在完全不修改原有date列的前提下，新增一个自动同步的生成列，为列建立索引提升排序速度，原有依赖date字符串的代码完全不需要改动：

1. 新增存储型生成列，列值自动从原date字段转换得到，原date列的字段类型、存储内容完全不变：

   ALTER TABLE table 
   ADD COLUMN sort_time TIMESTAMPTZ 
   GENERATED ALWAYS AS (to_timestamp(date, 'Dy Mon DD YYYY HH24:MI:SS GMT TZ')) STORED;
   

2. 为生成列建立排序索引：

   CREATE INDEX idx_table_sort_time ON table(sort_time DESC);
   

3. 后续查询直接替换排序字段为sort_time即可，查询性能会和原生存储时间戳的表无差异。

前置校验提示 #

上线前可以跑一次全表校验，筛选出格式不符合标准的date值，避免转换后生成空值影响排序结果。

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