Thread: BETWEEN not matching on timestamp value
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@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:
> 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"
No, it's complaining that the constant is out of range --- it's
failing long before it's tried to do any actual BETWEEN comparisons.
Surely you meant something more like 2008-08-09?
regards, tom lane
On Jun 30, 2009, at 7:17 PM, James B. Byrne wrote: > 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 Oh I so do hope that we won't have to bother with currencies anymore THAT far into the future! Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a4a4f2d759151169695543!
On Tue, Jun 30, 2009 at 11:45 AM, Alban Hertroys<dalroi@solfertje.student.utwente.nl> wrote: > On Jun 30, 2009, at 7:17 PM, James B. Byrne wrote: > >> 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 > > > Oh I so do hope that we won't have to bother with currencies anymore THAT > far into the future! You laugh, but who will be fixing the Y20M problem, huh?
On Tue, June 30, 2009 13:24, Tom Lane wrote: > > No, it's complaining that the constant is out of range --- it's > failing long before it's tried to do any actual BETWEEN comparisons. > Surely you meant something more like 2008-08-09? > > regards, tom lane > The column is a timestamp value. I expected any time on any given date to fall between the start and end of that day so the hh:mm:ss:hh portion does not leap out at me as something that should cause a problem. Should it? The application system times are all UTC. ALTER TABLE currency_exchange_rates ADD COLUMN effective_from timestamp without time zone; ALTER TABLE currency_exchange_rates ALTER COLUMN effective_from SET STORAGE PLAIN; ALTER TABLE currency_exchange_rates ALTER COLUMN effective_from SET NOT NULL; -- *** 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
On Tue, Jun 30, 2009 at 11:51 AM, James B. Byrne<byrnejb@harte-lyne.ca> wrote: > > On Tue, June 30, 2009 13:24, Tom Lane wrote: > >> >> No, it's complaining that the constant is out of range --- it's >> failing long before it's tried to do any actual BETWEEN comparisons. >> Surely you meant something more like 2008-08-09? >> >> regards, tom lane >> > > The column is a timestamp value. I expected any time on any given > date to fall between the start and end of that day so the > hh:mm:ss:hh portion does not leap out at me as something that should > cause a problem. Should it? 20080809-01-01 00:00:00 But that timestamp is for the year 20080809, which is pretty far off in the future. I think that's the problem.
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> The column is a timestamp value. I expected any time on any given
> date to fall between the start and end of that day so the
> hh:mm:ss:hh portion does not leap out at me as something that should
> cause a problem. Should it?
It's the eight-digit year field that it's unhappy with ...
regards, tom lane
On Tuesday 30 June 2009 10:48:50 am Scott Marlowe wrote: > Oh I so do hope that we won't have to bother with currencies anymore THAT > > > far into the future! > > You laugh, but who will be fixing the Y20M problem, huh? Not you or I that's for sure. -- John Fabiani