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