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

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

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.