通过Node.js操作PostgreSQL时日期比较功能不生效问题求助

问题根因 #

你遇到的问题本质是字符串逐位比较规则和你生成的时间戳格式不匹配共同导致的：

• 你数据库的timestamp字段是Text类型，执行>比较时用的是字符串字典序，而非时间大小逻辑
• 你生成的currentTimestamp小时位没有补前导零：参考值里的时间输出为2021-09-12 4:26:16，而数据库里存储的同天下午时间值是2021-09-12 16:25:35
  逐位对比两个字符串：

库内值：2021-09-12 16:25:35
传入值：2021-09-12 4:26:16

对比到日期后的空格后第一位时，1的ASCII码小于4，会直接判定库内值小于传入值，因此不会被返回，和你猜测的正午分界逻辑完全吻合。

解决方案 #

方案1：修复时间戳生成格式（快速修复） #

生成currentTimestamp时给小时、分钟、秒都补前导零，保证所有时间位都是两位，和库内存储的格式对齐，字符串比较结果就会和时间大小一致：

let currentDate = new Date()
let date = ("0" + currentDate.getDate()).slice(-2);
let month = ("0" + (currentDate.getMonth() + 1)).slice(-2);
let year = currentDate.getFullYear();
// 给小时、分钟、秒统一补零
let hours = ("0" + currentDate.getHours()).slice(-2);
let minutes = ("0" + currentDate.getMinutes()).slice(-2);
let seconds = ("0" + currentDate.getSeconds()).slice(-2);
let currentTimestamp = year + "-" + month + "-" + date + " " + hours + ":" + minutes + ":" + seconds

修复后相同时间点生成的时间戳会变成2021-09-12 04:26:16，此时和库内值对比就会正确判定16:25:35 > 04:26:16，符合你的查询逻辑。

方案2：修改字段类型为时间类型（推荐长期使用） #

Text类型存储时间会带来很多边界问题，比如时区偏差、格式不统一、索引效率低等，建议将字段改为PostgreSQL原生的timestamp或timestamptz类型：

1. 修改表结构：

ALTER TABLE stamp_table ALTER COLUMN timestamp TYPE timestamp USING timestamp::timestamp;

2. 查询时直接传入Date对象即可，Node.js的pg库会自动做类型转换，不需要手动格式化时间：

const client = await pool.connect()
let sqlStatement = 'SELECT * FROM stamp_table WHERE timestamp > $1'
let results = await client.query(sqlStatement, [new Date()]);

该方案彻底规避了字符串比较的逻辑错误，也不需要手动处理时间格式化的各种边界问题。

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