postgresql - 时间戳列上的 date_trunc 不返回任何内容

Question

在将截断字段与 date_trunc() 进行比较后从数据库中检索记录时，我遇到了一个奇怪的问题.

此查询不返回任何数据:

select id from my_db_log 
 where date_trunc('day',creation_date) >= to_date('2014-03-05'::text,'yyyy-mm-dd');

但是如果我添加列 creation_date与 id然后它返回数据(即 select id, creation_date... )。

我还有一个专栏last_update_date具有相同的类型，当我使用该类型时，仍然会执行相同的行为。

select id from my_db_log
 where date_trunc('day',last_update_date) >= to_date('2014-03-05'::text,'yyyy-mm-dd');

与上一个类似。如果我这样做它也会返回记录 id, last_update_date在我的 select .

现在进一步挖掘，我添加了 creation_date和 last_updated_date在我的 where子句，这次它要求将它们都放在我的 select 中有记录的子句(即 select id, creation_date, last_update_date )。

有没有人遇到过同样的问题？这种类似的方法适用于我的其他具有此类列的表!

如果有帮助，这是我的表架构:

id serial NOT NULL,
creation_date timestamp without time zone NOT NULL DEFAULT now(),
last_update_date timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT db_log_pkey PRIMARY KEY (id),

我之前问过一个不同的问题，但没有得到任何答案。这个问题可能与那个有关。如果你对那个感兴趣，这里是the link .

编辑: EXPLAIN (FORMAT XML)与 select *返回:

<explain xmlns="http://www.postgresql.org/2009/explain">
  <Query>
    <Plan>
      <Node-Type>Result</Node-Type>
      <Startup-Cost>0.00</Startup-Cost>
      <Total-Cost>0.00</Total-Cost>
      <Plan-Rows>1000</Plan-Rows>
      <Plan-Width>658</Plan-Width>
      <Plans>
        <Plan>
          <Node-Type>Result</Node-Type>
          <Parent-Relationship>Outer</Parent-Relationship>
          <Alias>my_db_log</Alias>
          <Startup-Cost>0.00</Startup-Cost>
          <Total-Cost>0.00</Total-Cost>
          <Plan-Rows>1000</Plan-Rows>
          <Plan-Width>658</Plan-Width>
          <Node/s>datanode1</Node/s>
          <Coordinator-quals>(date_trunc('day'::text, creation_date) &gt;= to_date('2014-03-05'::text, 'yyyy-mm-dd'::text))</Coordinator-quals>
        </Plan>
      </Plans>
    </Plan>
  </Query>
</explain>

Answer 1

“不可能”现象

返回的行数完全独立于 SELECT 子句中的项目。 (但请参阅@Craig 关于 SRF 的评论。)您的数据库中必须损坏。
也许是一个 splinter 的覆盖指数？当您放入附加列时，您会强制 Postgres 访问该表本身。尝试重新索引:

REINDEX TABLE my_db_log;

关于 REINDEX 的手册.或者:

VACUUM FULL ANALYZE my_db_log;

更好的查询

无论哪种方式，请改用:

select id from my_db_log 
where creation_date >= '2014-03-05'::date

或者:

select id from my_db_log 
where creation_date >= '2014-03-05 00:00'::timestamp

'2014-03-05' 采用 ISO 8601 格式。您可以将此字符串文字转换为 date。无需 to_date()，适用于任何 语言环境。 date 与 creation_date(timestamp [without time zone])。有关 Postgres 中时间戳的更多详细信息，请访问:
Ignoring timezones altogether in Rails and PostgreSQL

另外，在这里输入 date_trunc() 也没有任何好处。相反，您的查询会变慢，并且无法使用该列上的任何普通索引(可能使它变慢很多)