PostgreSQL无时区时间戳列匹配无结果问题求助

碰到这种明明数据存在却查不到的情况确实头疼，我来帮你一步步排查可能的原因和对应的解决办法：

1. 最可能的原因：隐藏的小数秒精度 #

PostgreSQL的timestamp without time zone类型支持微秒级的精度（最多6位小数），但很多时候插入数据时，系统会自动带上毫秒/微秒，而你查询时只写了到秒的时间，直接用=比较就会因为精度不匹配返回空结果。举个例子：如果实际存储的是2018-05-10 05:00:02.123456，但你查的是2018-05-10 05:00:02，这时候两者是不相等的。

怎么验证？ #

先查一下该列的完整值（包括小数部分）：

SELECT column, column::text FROM Test_DB.Test_Tab WHERE column::date = '2018-05-10';

如果看到目标时间后面跟着.xxx的小数，那就是这个问题了。

解决办法 #

• 方法一：用范围查询覆盖整秒的区间，这样不管有没有小数秒都能匹配到：

SELECT * FROM Test_DB.Test_Tab 
WHERE column >= '2018-05-10 05:00:02'::timestamp 
  AND column < '2018-05-10 05:00:03'::timestamp;

• 方法二：用date_trunc截断小数秒后再比较：

SELECT * FROM Test_DB.Test_Tab 
WHERE date_trunc('second', column) = '2018-05-10 05:00:02'::timestamp;

2. 容易忽略的时区解析问题 #

虽然你的列是timestamp without time zone，但当你直接用字符串'2018-05-10 05:00:02'查询时，PostgreSQL会根据当前会话的timezone参数把这个字符串解析成timestamp without time zone。如果你的会话时区和插入数据时的时区不一样，就可能导致解析后的时间和实际存储的时间不匹配。

怎么验证？ #

先看看当前会话的时区设置：

SHOW timezone;

然后可以试试显式指定时区转换后查询（比如假设数据是在UTC时区插入的）：

SELECT * FROM Test_DB.Test_Tab 
WHERE column = '2018-05-10 05:00:02'::timestamptz AT TIME ZONE 'UTC';

解决办法 #

要么确保当前会话的时区和插入数据时的时区一致，要么显式指定时区进行转换后再做比较。

3. 大小写敏感的标识符问题 #

PostgreSQL默认对表名、列名不区分大小写，但如果创建表或列的时候用了双引号指定了大小写（比如"Column"），那查询时必须严格匹配大小写，否则会找不到对应的列（相当于查了一个不存在的列，自然返回空）。

怎么验证？ #

查一下表的实际列名：

SELECT column_name FROM information_schema.columns 
WHERE table_schema = 'Test_DB' AND table_name = 'Test_Tab';

如果列名是带大小写的（比如首字母大写），那查询时要加双引号：

SELECT * FROM Test_DB.Test_Tab WHERE "Column" = '2018-05-10 05:00:02';

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