PostgreSQL：与datetime之间 [英] PostgreSQL: between with datetime

问题描述

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

I use PostgreSQL 8.4.11 and find strange error. When I query:

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间隔日期。

I would expect dates in interval 1-01-01 ... 1-12-31.

推荐答案

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

You expected 1-01-01 ... 1-12-31 ... but how is PostgreSQL supposed to know what you mean by that?

字符串文字ar e根据您当前的区域设置解释，特别是 lc_time 转换为 timestamp 或 date 。我引用了手册 here ：

String literals are interpreted according to your current locale settings, in particular lc_time when cast to timestamp or date. I quote the manual here:

lc_time（string）

lc_time (string)

设置用于格式化日期的区域设置和时代，例如与to_char系列的功能。可接受的值为
系统依赖;有关详细信息，请参见第22.1节。如果这个
变量设置为空字符串（这是默认值），则
值以
系统依赖的方式从服务器的执行环境继承。

Sets the locale to use for formatting dates and times, for example with the to_char family of functions. Acceptable values are system-dependent; see Section 22.1 for more information. If this variable is set to the empty string (which is the default) then the value is inherited from the execution environment of the server in a system-dependent way.

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

In your case, the mutilated timestamp literal 1-12-31 23:59:59 is obviously interpreted as:

D-MM-YY h24:mi:ss

虽然你希望：

Y-MM-DD h24:mi:ss

3个选项

3 options

1. 将 lc_time 设置为以与您相同的方式解释此类文字的区域设置。不确定有一个。

1. Set lc_time to a locale that interprets such a literal in the same way as you do. Not sure there is one.

使用 to_timestamp（） 以一种定义良好的方式解释字符串文字，与当前的语言环境无关。更好的。

Use to_timestamp() to interpret the string literal in a well defined way - independent of the current locale. Much better.

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

更好的是，使用 ISO 8601格式（ YYYY-MM-DD ）。那就是 与任何区域设置无歧义

Better yet, use ISO 8601 format (YYYY-MM-DD) for all datetime literals. That is unambiguous with any locale.

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

重写查询

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

Rewrite query

Finally, your query is faulty to begin with. Handle a range query differently. Rewrite your query to:

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 可能是您感兴趣的。

这篇关于PostgreSQL：与datetime之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！