Re: interval integer comparison
От | Havasvölgyi Ottó |
---|---|
Тема | Re: interval integer comparison |
Дата | |
Msg-id | 006201c56765$eddf4f40$6c00a8c0@OTTO обсуждение исходный текст |
Ответ на | interval integer comparison (Havasvölgyi Ottó <h.otto@freemail.hu>) |
Ответы |
Re: interval integer comparison
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: