PostgreSQL中如何基于timestamp类型进行时间查询？

用户问题场景 #

数据表结构及示例数据：

device_created_at字段为timestamp without time zone类型，执行以下查询未返回结果：

select * from device where to_char(device_created_at,'YYYY-MM-DD HH24:MI:SS.FFF') = '2022-08-10 20:55:16.695'

原因分析 #

用to_char转字符串匹配失败，核心问题是：

1. PostgreSQL的timestamp without time zone实际存储微秒级精度（6位小数），而FFF仅截取前三位毫秒，若原数据实际是2022-08-10 20:55:16.695123这类带微秒的数值，字符串格式化后看似匹配，但实际存储值与目标字符串转成的timestamp精度不匹配。
2. 字符串匹配会导致字段索引失效，且存在格式解析的潜在误差。

正确查询方案 #

1. 原生timestamp类型匹配（推荐）

直接将目标字符串转为timestamp类型与字段比较，PostgreSQL会自动处理精度兼容，同时能利用字段索引：

SELECT * FROM device 
WHERE device_created_at = '2022-08-10 20:55:16.695'::timestamp without time zone;

简写（PostgreSQL会自动识别字符串为timestamp类型）：

SELECT * FROM device 
WHERE device_created_at = '2022-08-10 20:55:16.695';

2. 时间范围匹配

若不确定精确微秒值，可查询目标时间点的区间范围，覆盖所有同毫秒段的记录：

SELECT * FROM device 
WHERE device_created_at >= '2022-08-10 20:55:16.695' 
AND device_created_at < '2022-08-10 20:55:16.696';

3. 精度截断后匹配

如果需要强制截断精度，推荐用date_trunc而非to_char：

SELECT * FROM device 
WHERE date_trunc('millisecond', device_created_at) = '2022-08-10 20:55:16.695'::timestamp;

重要提示 #

• 优先使用原生timestamp类型比较，避免字符串转换，既保证查询效率又消除格式误差。
• 若字段存在索引，字符串匹配会导致索引失效，大幅降低查询性能。

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