Strange results with date/interval arithmetic

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Strange results with date/interval arithmetic
Date: 2002-01-08 19:10:50
Message-ID: 200201081910.g08JAoJ13410@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I am seeing strange results from date/interval computations involving
months.

I get the correct answers because I have a negative setting relative to
GMT. Here are my results with TZ=EST5EDT:

test=> select '2001/3/1'::date - '1 month'::interval;
?column?
------------------------
2001-02-01 00:00:00-05
(1 row)

With GMT it is OK too:

test=> select '2001/3/1'::date - '1 month'::interval;
?column?
------------------------
2001-02-01 00:00:00+00
(1 row)

However, with GMT+1 I see a big failure:

test=> select '2001/3/1'::date - '1 month'::interval;
?column?
------------------------
2001-01-29 00:00:00+01
(1 row)

Why does it say 2001-01-29?

This is interesting:

test=> select '2001/7/1'::date - '1 month'::interval;
?column?
------------------------
2001-05-31 00:00:00+02
(1 row)

test=> select '2001/8/1'::date - '1 month'::interval;
?column?
------------------------
2001-07-01 00:00:00+02
(1 row)

Because August and July have the same number of months, it worked. I am
going to research this but someone may know the solution already.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2002-01-08 19:23:09 VPATH builds fail
Previous Message Bruce Momjian 2002-01-08 18:58:06 Re: Default permissions of system catalogs