This was supposed to go to the list. Sorry.
---------- Forwarded message ----------
From: "Michael Trausch" <mike@xxxxxxxxxx>
Date: Aug 8, 2012 10:12 AM
Subject: Re: Interval "1 month" is equals to interval "30 days" - WHY?
To: "Albe Laurenz" <laurenz.albe@xxxxxxxxxx>
From: "Michael Trausch" <mike@xxxxxxxxxx>
Date: Aug 8, 2012 10:12 AM
Subject: Re: Interval "1 month" is equals to interval "30 days" - WHY?
To: "Albe Laurenz" <laurenz.albe@xxxxxxxxxx>
There is root in accounting for this type of view of the interval. In accounting, a month is considered to have 30 days or 4.25 weeks, and a year is considered to have 360 days. The reason for this is that both the month and year are easier to work with when evenly divisible. A quarter then has 90 days (30 * 3 or 360 / 4), and certain other equalities can be held true.
If you need exact date math, be prepared to spend a *lot* of time on the problem. All exact date math operations must have a starting point, and "exact" has different meanings depending on the application. Good luck.
On Aug 8, 2012 5:55 AM, "Albe Laurenz" <laurenz.albe@xxxxxxxxxx> wrote:
Dmitry Koterov wrote:
>> I've just discovered a very strange thing:
>>
>> SELECT '1 mon'::interval = '30 days'::interval --> TRUE???
>>
>> This returns TRUE (also affected when I create an unique index using
an
>> interval column). Why?
>>
>> I know that Postgres stores monthes, days and seconds in interval
values
>> separately. So how to make "=" to compare intervals "part-by-part"
and not
>> treat "1 mon" as "30 days"?
>>
>> P.S.
>> Reproduced at least in 8.4 and 9.1.
> ...and even worse:
>
> SELECT ('1 year'::interval) = ('360 days'::interval); --> TRUE :-)
> SELECT ('1 year'::interval) = ('365 days'::interval); --> FALSE :-)
Intervals are internally stored in three fields: months, days
and microseconds. A year has 12 months.
PostgreSQL converts intervals into microseconds before comparing them:
a month is converted to 30 days, and a day is converted to 24 hours.
Of course this is not always correct.
But what should the result of
INTERVAL '1 month' = INTERVAL '30 days'
be? FALSE would be just as wrong.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
