to_char() issue in PostgreSQL

Question

我有一个PostgreSQL表，其中有一个名为effective_date的字段，数据类型是integer（epoch日期）。我想做的是只选择具有我选择的生效日期的条目（我只想按月份查询）。我的查询在下面，问题是，它没有返回任何内容，尽管表中确实有许多符合选择条件的条目。

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

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";

如果它是integer，那么-假设它是一个历元日期-你必须将其转换为带有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";

问题的直接原因是您使用整数epoch日期调用to_char(integer,text)。只有to_char的timestamp版本进行日期格式化；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注入。