☰

BETWEEN with datetime 質問する

tag-icon • tag-icon sql postgresql datetime
BETWEEN with datetime 質問する

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 の範囲内の日付を期待します。

ベストアンサー1

予想した日付「1-01-01 ～ 1-12-31 の間隔」しかし、
PostgreSQL はそれが何を意味するのかをどうやって知るのでしょうか?

入力文字列リテラルは、現在のセッションの設定に従って解釈されます（postgressql.conf上書きされない限り、デフォルトで一般的な設定になります）。特にdatestyle:

DateStyle（string）

日付と時刻の値の表示形式、およびあいまいな日付入力値の解釈規則を設定します。歴史的理由により、この変数には 2 つの独立したコンポーネントが含まれています。出力形式の指定 ( ISO、Postgres、SQL、またはGerman) と、年/月/日の順序の入力/出力の指定 ( DMY、MDY、またはYMD) です。これらは別々に設定することも、一緒に設定することもできます。キーワードEuroと はEuropeanの同義語です。DMYキーワードUS、NonEuro、 はNonEuropeanの同義語ですMDY。セクション8.5詳細については、 を参照してください。組み込みのデフォルトは ですISO, MDYが、initdb は、選択したロケールの動作に対応する設定で構成ファイルを初期化しますlc_time。

（出力形式は主に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) はすべての日付時刻リテラルに適用されます。つまり明確で、あらゆる設定から独立している。

     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'
AND    d.given_on <  '2001-02-01'
ORDER  BY d.created_on DESC;

'2001-02-01'完全に有効なタイムスタンプ入力です。参照:

範囲タイプPostgreSQL 9.2 以降では興味深いかもしれません。

おすすめ記事