Re: interval integer comparison

Hi,

----- Original Message -----
From: "Bruno Wolff III" <bruno(at)wolff(dot)to>
To: "Havasvölgyi Ottó" <h(dot)otto(at)freemail(dot)hu>
Cc: <pgsql-general(at)postgresql(dot)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(dot)otto(at)freemail(dot)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