From: | Dmitry Koterov <dmitry(at)koterov(dot)ru> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Interval "1 month" is equals to interval "30 days" - WHY? |
Date: | 2012-08-08 11:27:36 |
Message-ID: | CA+CZih6CdRpRES8_yahkAYfypzzxe2mYCcTZJvMVd-+zOMVpRQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Of course NOT.
'1 mon' and '30 days' have different meaning. So they should not be equal.
I understand that conversion to seconds is a more or less correct way to
compare intervals with ">" and "<". But equality is not the same as
ordering (e.g. equality is typically used in JOINs and unique indices).
Now I have to use CREATE UNIQUE INDEX test ON tbl(interval_col::TEXT) and
use the same casting to TEXT in all JOINS and searches - this is very ugly.
On Wed, Aug 8, 2012 at 1:54 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-08-08 11:57:01 | Re: Interval "1 month" is equals to interval "30 days" - WHY? |
Previous Message | gwainer | 2012-08-08 11:27:11 | Re: postgis and pgpool |