Re: PG handling of date expressions

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/