Thread: interval integer comparison
Hi all, Pg 8.0.3 allows me to compare interval with integer, but I cannot see any reasonable rule: These are true: 1 < '1 days'::interval 2 > '1 days'::interval 999 > '1 days'::interval 1999 < '2 days'::interval 2000 != '2 days'::interval 2001 > '2 days'::interval ... 20999 < '21 days'::interval 21000 != '21 days'::interval 21001 > '21 days'::interval and so on Is this a bug? Best Regards, Otto
=?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu> writes:
> Pg 8.0.3 allows me to compare interval with integer, but I cannot see any
> reasonable rule:
> 1 < '1 days'::interval
The reason that doesn't fail outright is that both integer and
interval have implicit coercions to text. So the only interpretation
the parser can find is to convert both sides to text and use the text <
operator. As text comparisons your answers all make sense.
I've been arguing for a long time that we need to cut down on the number
of implicit coercions to text...
regards, tom lane
Thank you Tom. It was a bit confusing because my WHERE clause looked something like this: ... WHERE date_field - current_date < '21 days'::interval; And then I got records, whose with date_field's year was 2010. :-o Now I am using this formula: ... WHERE date_field < current_date + '21 days'::interval; Best Regards, Otto ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Havasvölgyi Ottó" <h.otto@freemail.hu> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, May 31, 2005 5:46 PM Subject: Re: [GENERAL] interval integer comparison > =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu> writes: > > Pg 8.0.3 allows me to compare interval with integer, but I cannot see any > > reasonable rule: > > > 1 < '1 days'::interval > > The reason that doesn't fail outright is that both integer and > interval have implicit coercions to text. So the only interpretation > the parser can find is to convert both sides to text and use the text < > operator. As text comparisons your answers all make sense. > > I've been arguing for a long time that we need to cut down on the number > of implicit coercions to text... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
On Thu, Jun 02, 2005 at 01:54:12 +0200, Havasvölgyi Ottó <h.otto@freemail.hu> wrote: > Thank you Tom. > > It was a bit confusing because my WHERE clause looked something like this: > > ... WHERE date_field - current_date < '21 days'::interval; > > And then I got records, whose with date_field's year was 2010. :-o > Now I am using this formula: > > ... WHERE date_field < current_date + '21 days'::interval; If date_field and current_date are realy of type date (and not say some timestamp varient), then you should use: ... WHERE date_field < current_date + 21 What you used above may have unexpected results near a daylight savings time change as the data will be promoted to timestamps to do the comparison.
Hi, ----- Original Message ----- From: "Bruno Wolff III" <bruno@wolff.to> To: "Havasvölgyi Ottó" <h.otto@freemail.hu> Cc: <pgsql-general@postgresql.org> Sent: Thursday, June 02, 2005 3:53 AM Subject: Re: [GENERAL] interval integer comparison > On Thu, Jun 02, 2005 at 01:54:12 +0200, > Havasvölgyi Ottó <h.otto@freemail.hu> wrote: > > Thank you Tom. > > > > It was a bit confusing because my WHERE clause looked something like this: > > > > ... WHERE date_field - current_date < '21 days'::interval; > > > > And then I got records, whose with date_field's year was 2010. :-o > > Now I am using this formula: > > > > ... WHERE date_field < current_date + '21 days'::interval; > > If date_field and current_date are realy of type date (and not say > some timestamp varient), then you should use: > ... WHERE date_field < current_date + 21 > > What you used above may have unexpected results near a daylight savings > time change as the data will be promoted to timestamps to do the > comparison. > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > > I tried to simulate this unexpected result, but with no success. Here in Hungary we had daylight saving this year on the 27th of March (http://webexhibits.org/daylightsaving/b.html). So I tried these: select '2005-03-28'::date - '1 day'::interval; select '2005-03-28'::timestamp - '1 day'::interval; select '2005-03-28'::date - '24 hour'::interval; select '2005-03-28'::timestamp - '24 hour'::interval; Each of the results were the same: 2005-03-27 00:00:00 I tried with a larger interval too but I didn't experience any shift in hours. By the way, I use PG 8.0.3 on WinXP SP1, I just experiment with Postgres. Best Regards, Otto
On Thu, Jun 02, 2005 at 13:26:32 +0200, Havasvölgyi Ottó <h.otto@freemail.hu> wrote: > > I tried to simulate this unexpected result, but with no success. Here in > Hungary we had daylight saving this year on the 27th of March > (http://webexhibits.org/daylightsaving/b.html). So I tried these: > > select '2005-03-28'::date - '1 day'::interval; > select '2005-03-28'::timestamp - '1 day'::interval; > select '2005-03-28'::date - '24 hour'::interval; > select '2005-03-28'::timestamp - '24 hour'::interval; > > Each of the results were the same: 2005-03-27 00:00:00 > > I tried with a larger interval too but I didn't experience any shift in > hours. By the way, I use PG 8.0.3 on WinXP SP1, I just experiment with > Postgres. I double checked and the promotion is to TIMESTAMP WITHOUT TIME ZONE so so daylight savings won't in fact be a problem. However, subtracting an integer will avoid the conversion and should run slightly faster.