PostgreSQL: between with datetime

Question

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

I get results:

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

Why?

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

Answer 1

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

Input string literals are interpreted according to the settings of your current session (which defaults to general settings in postgressql.conf unless overruled). In particular datestyle:

DateStyle (string)

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

(While output format is mostly determined by lc_time.)

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

D-MM-YY h24:mi:ss

While you would have hoped for:

Y-MM-DD h24:mi:ss

3 options

1. Set datestyle so that it interprets literals in the same way as you do. Maybe ISO, YMD?

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

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

3. Better yet, use ISO 8601 format (YYYY-MM-DD) for all datetime literals. That is unambiguous and independent from any settings.

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

Rewrite query

Your query is faulty to begin with. Handle range queries differently. Like:

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;

Or, simpler yet:

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 in PostgreSQL 9.2 or newer may be of interest.