BETWEEN not matching on timestamp value

From: "James B(dot) Byrne" <byrnejb(at)harte-lyne(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: BETWEEN not matching on timestamp value
Date: 2009-06-30 17:17:29
Message-ID: 54647.216.185.71.24.1246382249.squirrel@webmail.harte-lyne.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have encountered an error that, on the face of it, seems to me to
be inexplicable. I hope that someone here can illuminate the matter
for me.

PGError: ERROR: timestamp out of range: "20080809-01-01
00:00:00"
: SELECT * FROM "currency_exchange_rates" WHERE
(currency_code_base = E'CAD' AND currency_code_quote = E'JPY'
AND effective_from BETWEEN '20080809-01-01 00:00:00' AND
'20080809-01-01 23:59:59') ORDER BY currency_code_base,
currency_code_quote, effective_from DESC

Now, if I read this aright then, this is telling me that the
timestamp value I am processing is "20080809-01-01 00:00:00"

It is also telling me that this value does not lie between:

'20080809-01-01 00:00:00' AND '20080809-01-01 23:59:59'

If this is true then the BETWEEN operator must be EXCLUSIVE of its
endpoints. However the manual says this about BETWEEN

In addition to the comparison operators, the special BETWEEN
construct is available.

a BETWEEN x AND y

is equivalent to

a >= x AND a <= y

Which says to me that the BETWEEN operator should be an INCLUSIVE
match. SO, what is happening here? Have I missed something dead
obvious?

--
*** E-Mail is NOT a SECURE channel ***
James B. Byrne mailto:ByrneJB(at)Harte-Lyne(dot)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2009-06-30 17:22:23 Re:
Previous Message Alan McKay 2009-06-30 16:48:02 Re: Help with installation please...