PostgreSQL中的to_char()问题

Question

我有一个名为effective_date的PostgreSQL表格，数据类型为整数(时期标记)。 我想要做的是选择只有我选择的effective_date的条目（我只想按月查询）。 我的查询如下，问题在于，它没有返回任何内容，尽管表中确实有很多符合选择标准的条目。

$query = "select    *
          from ". $this->getTable() ."
          where pay_stub_entry_name_id = 43
          AND to_char(effective_date, 'Mon') = 'Jul'
          AND deleted = 0";

- Les_Salantes

---

等一下，你说 effective_date 的数据类型是 integer？不是 date？也不是 timestamp？这是你问题中的一个错误吗？如果它是一个整数，你怎么期望从中得到一个日期呢？它是一个纪元日期（自某个日期以来的秒数）吗？ - Craig Ringer

@Craig 对不起，是我的错误。是一个纪元日期。 - Les_Salantes

那么，to_char会做什么呢？你在psql中试过了吗？让我们看看：select to_char( extract(epoch from current_timestamp), 'Mon')只输出字符串Mon，它永远不会等于字符串Jul。所以问题就在这里，你调用了to_char的整数版本，并期望它知道你想将整数视为日期。在psql中查看\df to_char以查看所有to_char变体。根据文档，只有timestamp版本适用于日期格式化。 - Craig Ringer

@CraigRinger 谢谢，它起作用了。你解释得很好。 :) - Les_Salantes

Answer 1

请使用extract(month from the_date)代替to_char。请参考Pg文档中的日期时间函数。

如果你使用to_char，你将会遇到许多大小写、本地化等各种问题。

假设你指的是effective_date的数据类型为timestamp或date，那么代码应该如下：

$query = "select    *
      from ". $this->getTable() ."
      where pay_stub_entry_name_id = 43
      AND extract(month from effective_date) = 7
      AND deleted = 0";

如果它是一个整数，那么假设它是一个纪元日期-您必须使用to_timestamp将其转换为时间戳，然后在其上使用extract。请参见上面链接的文档中的epoch部分，例如:

$query = "select    *
      from ". $this->getTable() ."
      where pay_stub_entry_name_id = 43
      AND extract(month from to_timestamp(effective_date)) = 7
      AND deleted = 0";

你的问题的直接原因是你使用了一个整数时期日期来调用to_char(integer, text)。只有timestamp版本的to_char适用于日期格式化，对于其他版本而言，Mon并没有特殊含义，所以它只是作为字面值字符串Mon输出。比较如下：

regress=# SELECT to_char(current_timestamp, 'Mon');
 to_char 
---------
 Aug
(1 row)

regress=# select to_char( extract(epoch from current_timestamp), 'Mon');
 to_char 
---------
 Mon
(1 row)

请记得对你的现实世界版本的这些查询进行参数化，以帮助避免SQL注入。

- Craig Ringer