From: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Cc: | <dmitry(at)koterov(dot)ru> |
Subject: | Re: Interval "1 month" is equals to interval "30 days" - WHY? |
Date: | 2012-08-08 09:54:02 |
Message-ID: | D960CB61B694CF459DCFB4B0128514C20841979F@exadv11.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Manoj Agarwal | 2012-08-08 10:43:07 | Re: File system level backup |
Previous Message | John R Pierce | 2012-08-08 07:46:34 | Re: Installation faillure version 8.4.12 |