PostgreSQL: zwischen mit datetime

Verwende ich PostgreSQL 8.4.11 und finden seltsame Fehler. Wenn ich Abfrage:

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

Bekomme ich die Ergebnisse:

  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
 ...

Warum?

Ich würde erwarten, dass dates im Intervall 1-01-01 ... 1-12-31.

InformationsquelleAutor Denis SkS | 2012-09-14

  1. 22

    Erwartet 1-01-01 ... 1-12-31 ... aber wie ist PostgreSQL eigentlich wissen, was du damit meinst?

    String-Literale interpretiert werden gemäß der aktuellen locale-Einstellungen, insbesondere lc_time wenn der Zauber zu timestamp oder date. Ich zitiere das Handbuch hier:

    lc_time (Zeichenkette)

    Setzt die locale für die Formatierung von Datums-und Zeitangaben, zum Beispiel mit der Familie der to_char-Funktionen. Zulässige Werte sind
    system-abhängig; siehe Abschnitt 22.1 weitere Informationen. Wenn diese
    variable auf eine leere Zeichenfolge festgelegt (die Standardeinstellung), dann wird das
    Wert wird geerbt von der Umgebung, die zur Ausführung der server in einem
    system-abhängigen Weise.

    In Ihrem Fall, den verstümmelten timestamp-literal 1-12-31 23:59:59 ist offensichtlich interpretiert als:

    D-MM-YY h24:mi:ss

    Während Sie sich vielleicht erhofft hätte:

    Y-MM-DD h24:mi:ss

    3 Optionen

    1. Set lc_time zu einem Gebietsschema interpretiert literal in der gleichen Weise, wie Sie tun. Nicht sicher, dass es eins ist.

    2. Verwenden to_timestamp() zu interpretieren, das string-literal in einem gut definierten Weise - unabhängig von der aktuellen locale. Viel besser.

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

    3. Besser noch, verwenden Sie ISO-8601-format (YYYY-MM-DD) für alle datetime-Literale sind. Das ist eindeutig mit jedem locale.

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

    Rewrite-query

    Endlich, deine query ist fehlerhaft, mit zu beginnen. Griff eine Reihe Abfrage anders. Schreiben Sie Ihre Abfrage:

    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;

    Oder, einfacher noch:

    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;

    Den neue range-Typen von PostgreSQL 9.2 kann für Sie von Interesse sein.

    Dank für die Antwort! Ich denke, ich mache die Dritte option im Montag.

    InformationsquelleAutor Erwin Brandstetter

  2. 1

    SELECT '1-12-31 23:59:59.999999'::timestamp; zurück 2031-01-12 23:59:59.999999 offenbar Postgres nicht als Jahr ohne Jahrhundert als erstes element in das Datum.

    InformationsquelleAutor lanzz

  3. 1

    Ihnen nicht sagen, in welches format Sie wollte es. So es gibt wieder das native format. Vielleicht ist Sie auch davon ausgegangen, dass jeder bezeichnet die Zeit, wie Sie es tun?
    Haben Sie einen Blick auf die möglichen Formate. http://www.postgresql.org/docs/8.2/static/functions-formatting.html

    InformationsquelleAutor Sjuul Janssen

Schreibe einen Kommentar