Thread: PG handling of date expressions

I encountered a situation wrt date expressions that, although I
eventually resolved, has left me with a few unanswered questions
regarding how PG handles dates.

My error was in not encapsulating a programmically inserted date
string within quotation marks.  This meant that I was sending off a
where clause that looked somewhat like this:

 WHERE 'date_of_interest' <= 2009-07-18

Now, as the date of interest was, in all but one case, prior to 1970
this appeared to work.  However, in one case the date was in 1999
and this was the record that exposed the error.  I extrapolated,
perhaps incorrectly, from my *nix experience and inferred that the
timestamp value 'date_of_interest' used a *nix epoch time value and
that the expression 2009-07-18 was resolving to 1984 at the DBMS.
If true then this would account for the behaviour observed.

However, it occurred to me that using the *nix epoch would be a very
odd thing for a DBMS. So, on reconsideration I thought that perhaps
the DBMS was using 1984 as the year value for comparison, which
would also explain the observed behaviour.

My question is: What is actually going on at teh DBMS when one sends
a conditional clause comparing a date to a numeric expression such
as the one above?

--
***          E-Mail is NOT a SECURE channel          ***
James B. Byrne                mailto:ByrneJB@Harte-Lyne.ca
Harte & Lyne Limited          http://www.harte-lyne.ca
9 Brockley Drive              vox: +1 905 561 1241
Hamilton, Ontario             fax: +1 905 561 0757
Canada  L8E 3C3

"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> My error was in not encapsulating a programmically inserted date
> string within quotation marks.  This meant that I was sending off a
> where clause that looked somewhat like this:

>  WHERE 'date_of_interest' <= 2009-07-18

Ah.  You are apparently using a pre-8.3 PG release, and you have just
stumbled across one of the commoner sorts of errors that prompted us
to remove a lot of implicit casts in 8.3.  The folks who screamed about
us doing that apparently hadn't yet made this type of mistake, or they'd
not have groused so much.

First off, the interpretation of the righthand side is obvious: it's a
numeric expression yielding the value 1984 (2009 minus 7 minus 18).
The parser is then faced with an expression
    date_column <= integer
There is no "<=" operator that compares dates and integers.  However,
there is a "text <= text" operator, and before 8.3 there were implicit
casts from date to text and from integer to text.  So the parser decided
the correct interpretation of your command was
    date_column::text <= '1984'::text
which indeed matches your description of how it acted.  It would have
behaved very strangely indeed if you'd been using a non-ISO datestyle,
though ...

8.3 and up will throw an error on such cases:

regression=# select current_date <= 2009-07-18;
ERROR:  operator does not exist: date <= integer
LINE 1: select current_date <= 2009-07-18;
                            ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

            regards, tom lane

On Sun, Jul 19, 2009 at 01:20:27PM -0400, James B. Byrne wrote:
> I was sending off a where clause that looked somewhat like this:
>
>  WHERE 'date_of_interest' <= 2009-07-18
>
> Now, as the date of interest was, in all but one case, prior to 1970
> this appeared to work.  However, in one case the date was in 1999
> and this was the record that exposed the error.  I extrapolated,
> perhaps incorrectly, from my *nix experience and inferred that the
> timestamp value 'date_of_interest' used a *nix epoch time value and
> that the expression 2009-07-18 was resolving to 1984 at the DBMS.
> If true then this would account for the behaviour observed.

I assume you're not using PG 8.3 or later?  This would catch this sort
of mistake and complain that you're trying to compare an integer and a
date.

The reason you're getting 1984 is because the numeric expression (2009
- 7 - 18) equals 1984.  PG would then (silently) coerce both sides
of the comparison to a common type (i.e. text) and then end up doing
a lexicographical comparison on the two.  This is the reason that PG
started complaining about doing this silent casting and forcing users to
explicitly say that this is what they want.

--
  Sam  http://samason.me.uk/