Hi, ----- Original Message ----- From: "Bruno Wolff III" <bruno@xxxxxxxx> To: "Havasvölgyi Ottó" <h.otto@xxxxxxxxxxx> Cc: <pgsql-general@xxxxxxxxxxxxxx> Sent: Thursday, June 02, 2005 3:53 AM Subject: Re: interval integer comparison > On Thu, Jun 02, 2005 at 01:54:12 +0200, > Havasvölgyi Ottó <h.otto@xxxxxxxxxxx> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
