sql - PostgreSQL:在日期时间之间

Question

我使用 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 之间。

Answer 1

您期望 1-01-01 ... 1-12-31 ... 但是 PostgreSQL 应该如何知道您的意思？

根据当前 session 的设置解释输入字符串文字(默认为 postgressql.conf 中的常规设置，除非被否决)。特别是datestyle :

DateStyle (string)

Sets the display format for date and time values, as well as the rulesfor interpreting ambiguous date input values. For historical reasons,this variable contains two independent components: the output formatspecification (ISO, Postgres, SQL, or German) and theinput/output specification for year/month/day ordering (DMY, MDY,or YMD). These can be set separately or together. The keywordsEuro and European are synonyms for DMY; the keywords US,NonEuro, and NonEuropean are synonyms for MDY. See Section8.5 for more information. The built-in default is ISO, MDY, but initdb will initialize the configuration file with a setting thatcorresponds to the behavior of the chosen lc_time locale.

(虽然输出格式主要由 lc_time 决定。)

在您的例子中，残缺的时间戳文字 1-12-31 23:59:59 显然被解释为:

D-MM-YY h24:mi:ss

虽然你会希望:

Y-MM-DD h24:mi:ss

3个选项

1. 设置 datestyle 以便它以与您相同的方式解释文字。也许 ISO、YMD？

2. 使用 to_timestamp()以明确定义的方式解释字符串文字 - 独立于其他设置。好多了。

    SELECT to_timestamp('1-12-31 23:59:59', 'Y-MM-DD h24:mi:ss');
   

3. 更好的是，对所有日期时间文字使用 ISO 8601 格式 (YYYY-MM-DD)。即unambiguous and independent from any settings .

    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;

Range types在 PostgreSQL 9.2 或更新版本中可能会有兴趣。