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

Dmitry Koterov wrote:
>>> I've just discovered a very strange thing:
>>>
>>> SELECT '1 mon'::interval = '30 days'::interval --> TRUE???

>> 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.

> Of course NOT.
>
> '1 mon' and '30 days' have different meaning. So they should not be
equal.

Then maybe you should use something like this for equality:

CREATE OR REPLACE FUNCTION int_equal(interval, interval) RETURNS boolean
IMMUTABLE STRICT LANGUAGE sql AS
'SELECT
12 * EXTRACT (YEAR FROM $1) + EXTRACT (MONTH FROM $1)
= 12 * EXTRACT (YEAR FROM $2) + EXTRACT (MONTH FROM $2)
AND EXTRACT (DAY FROM $1) = EXTRACT (DAY FROM $2)
AND 3600000000 * EXTRACT (HOUR FROM $1)
+ 60000000 * EXTRACT (MINUTE FROM $1)
+ EXTRACT (MICROSECONDS FROM $1)
= 3600000000 * EXTRACT (HOUR FROM $2)
+ 60000000 * EXTRACT (MINUTE FROM $2)
+ EXTRACT (MICROSECONDS FROM $2)';

> 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.

A unique index on intervals is an interesting thing.
I guess you have a use case for it.

If I searched for an interval of '1 day' and the entry '24 hours'
would not be found, I'd be slightly disappointed, even if they are
sometimes not equal.
I guess it depends on the application.

As for the ugliness:
If you use a function like the above, the queries would
simply look like

SELECT ... FROM ... WHERE int_equal(int_col, my_int);

which is not too bad, right?

Yours,
Laurenz Albe