PostgreSQL精确匹配时间戳无结果？正确时间查询方法解析

精确匹配失效的核心原因 #

PostgreSQL的timestamp（包含timestamp without time zone和timestamptz）类型默认存储微秒级精度（部分版本支持纳秒），而你用to_char输出的结果仅展示到秒，会隐藏时间戳的小数部分（比如实际存储值可能是2022-04-19 19:01:11.567890）。

当执行update_timestamp = '2022-04-19 19:01:11'时，PostgreSQL会自动将字符串转换为秒级精度的时间戳（即2022-04-19 19:01:11.000000），这和实际存储的带小数的时间戳不相等，因此无法匹配到记录。而范围查询（比如update_timestamp between '2022-04-19 19:01:10' and '2022-04-19 19:01:12'）会包含所有秒数在该区间内的时间戳，自然能命中目标记录。

正确的查询方式 #

1. 匹配到秒级别（推荐，支持索引） #

如果仅需要匹配到秒的时间，优先用范围查询，这种方式可以利用字段上的索引，查询效率更高：

SELECT * FROM my_table 
WHERE update_timestamp >= '2022-04-19 19:01:11' 
  AND update_timestamp < '2022-04-19 19:01:12';

也可以用date_trunc函数截断时间戳到秒后匹配，但这种写法可能无法利用索引（除非创建函数索引）：

SELECT * FROM my_table 
WHERE date_trunc('second', update_timestamp) = '2022-04-19 19:01:11'::timestamp;

2. 精确匹配完整时间戳 #

如果需要完全匹配存储的时间戳，先查询出目标记录的完整时间值：

SELECT update_timestamp FROM my_table 
WHERE date_trunc('second', update_timestamp) = '2022-04-19 19:01:11'::timestamp;

得到完整的时间戳（比如2022-04-19 19:01:11.567890）后，再用这个值做精确匹配：

SELECT * FROM my_table WHERE update_timestamp = '2022-04-19 19:01:11.567890'::timestamp;

3. 字符串转时间戳的正确写法 #

两种写法均有效，但需注意字段类型：

• 若字段是timestamp without time zone，直接转换即可：

  '2022-04-19 19:01:11'::timestamp
  -- 或
  to_timestamp('2022-04-19 19:01:11', 'YYYY-MM-DD HH24:MI:SS')
  

• 若字段是timestamptz（带时区），建议明确指定时区，避免会话时区差异导致的转换错误：

  '2022-04-19 19:01:11+08'::timestamptz
  -- 或
  to_timestamp('2022-04-19 19:01:11+08', 'YYYY-MM-DD HH24:MI:SSTZ')
  

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