Search Postgresql Archives

Re: Interval "1 month" is equals to interval "30 days" - WHY?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux