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@xxxxxxxxxxxxx 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
