1. 首页
  2. 问题
  3. PostgreSQL：与日期时间之间
一尘不染

PostgreSQL：与日期时间之间

sql

我使用PostgreSQL 8.4.11并发现了奇怪的错误。当我查询时：

SELECT "documents_document"."given_on" 
FROM "documents_document" 
WHERE (EXTRACT('month' FROM "documents_document"."given_on") = 1
       AND "documents_document"."given_on" 
       BETWEEN '1-01-01 00:00:00' and '1-12-31 23:59:59.999999') 
ORDER BY "documents_document"."created_on" DESC

我得到结果：

  given_on  
------------
 2002-01-16
 2011-01-25
 2012-01-12
 2012-01-12
 2012-01-12
 2012-01-20
 2012-01-19
 2012-01-13
 2012-01-31
 2012-01-16
 2012-01-31
 2012-01-12
 ...

为什么？

我希望日期在1-01-01 … 1-12-31之间。


阅读 225

收藏
2021-03-17

共1个答案

一尘不染

您期望1-01-01 ... 1-12-31……但是PostgreSQL应该如何理解您的意思呢？

字符串文字会根据您当前的语言环境设置进行解释，尤其是lc_time当转换为timestamp或时date。我在这里引用手册：

lc_time（字符串）

设置用于格式化日期和时间的语言环境，例如to_char系列函数。可接受的值取决于系统；有关更多信息，请参见第22.1节。如果将此变量设置为空字符串（这是默认值），则该值将以与系统有关的方式从服务器的执行环境继承。

在您的情况下，残缺的时间戳文字1-12-31 23:59:59显然被解释为：

D-MM-YY h24:mi:ss

虽然您希望：

Y-MM-DD h24:mi:ss

3种选择

  1. 设置lc_time为以与您相同的方式解释此类文字的语言环境。不确定是否有一个。

  2. 用于to_timestamp()以明确定义的方式解释字符串文字-与当前语言环境无关。好多了。

    SELECT to_timestamp('1-12-31 23:59:59', 'D-MM-YY h24:mi:ss')

  3. 更好的是，对所有日期时间文字使用 ISO 8601格式 （YYYY-MM-DD）。这 在任何语言环境中 都是 明确的 。

    SELECT '2001-12-31 23:59:59'::timestamp

重写查询

最后，您的查询一开始是错误的。以不同的方式处理范围查询。将查询重写为：

SELECT d.given_on 
FROM   documents_document d
WHERE  EXTRACT('month' FROM d.given_on) = 1
AND    d.given_on >= '2001-01-01 0:0'
AND    d.given_on <  '2002-01-01 0:0'
ORDER  BY d.created_on DESC;

或者，更简单一些：

SELECT d.given_on 
FROM   documents_document d
WHERE  d.given_on >= '2001-01-01 0:0'
AND    d.given_on <  '2001-02-01 0:0'
ORDER  BY d.created_on DESC;

您可能会对PostgreSQL
9.2的新范围类型感兴趣。

2021-03-17