Re: [GENERAL] BETWEEN not matching on timestamp value
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BETWEEN not matching on timestamp value]
On Tue, June 30, 2009 14:07, Tom Lane wrote: > > > It's the eight-digit year field that it's unhappy with ... > Duuuh! I suppose that it would... Thanks, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@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 -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BETWEEN not matching on timestamp value
"James B. Byrne" 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BETWEEN not matching on timestamp value
On Tue, Jun 30, 2009 at 11:51 AM, James B. Byrne 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BETWEEN not matching on timestamp value
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. Byrnemailto:byrn...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BETWEEN not matching on timestamp value
On Tue, Jun 30, 2009 at 11:45 AM, Alban Hertroys 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BETWEEN not matching on timestamp value
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! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] BETWEEN not matching on timestamp value
"James B. Byrne" 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
