Thread: 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???
>>
>> 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

On 08/08/2012 05:54 PM, Albe Laurenz wrote:
> 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.

NULL? In all honesty, it's a reasonable fit for NULL in its
"uncertain/unknowable" personality,  because two intervals that don't
use the same time scales aren't truly comparable for equality. After all:

regress=# SELECT
         d1,
         d1 + INTERVAL '1 month' AS "d1 + 1 month",
         d1 + INTERVAL '28 days' AS "d1 + 28 days",
         (d1 + INTERVAL '28 days') - (d1 + INTERVAL '1 month') AS
"days_between_dates",
         d1 + INTERVAL '1 month' = d1 + INTERVAL '28 days' AS
"result_dates_equal",
         INTERVAL '1 month' = INTERVAL '28 days' AS "intervals_equal"
FROM (VALUES (DATE '2004-02-01'),('2005-02-01')) x(d1);
      d1     |    d1 + 1 month     |    d1 + 28 days     |
days_between_dates | result_dates_equal | intervals_equal
------------+---------------------+---------------------+--------------------+--------------------+-----------------
  2004-02-01 | 2004-03-01 00:00:00 | 2004-02-29 00:00:00 | -1
days            | f                  | f
  2005-02-01 | 2005-03-01 00:00:00 | 2005-03-01 00:00:00 |
00:00:00           | t                  | f
(2 rows)


shows that the very idea of interval equality comparison is nonsense
anyway, because it depends utterly on the date/time/timestamp to which
the interval is being applied.

Of course, NULL is horrid to work with, so I'm not sure it's really the
right answer. Defining an arbitrary 30 day month is bad enough in
functions like justify_days, though, and doing it implicitly in
comparisons seems wrong.

Summary: Intervals are icky.

--
Craig Ringer

Should now plus 157785000 seconds in text be NULL, because we don't
know how many "leap seconds" will be added?

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

Craig Ringer wrote:
>> 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.

> NULL? In all honesty, it's a reasonable fit for NULL in its
> "uncertain/unknowable" personality,  because two intervals that don't
> use the same time scales aren't truly comparable for equality.

Hmmm.  How would you define an ordering in that case?
And without an ordering, you couldn't use "btree" indexes
on interval columns, right?

Yours,
Laurenz Albe

On 08/08/2012 08:56 PM, Albe Laurenz wrote:
>
> Hmmm.  How would you define an ordering in that case?
> And without an ordering, you couldn't use "btree" indexes
> on interval columns, right?
Or, as Anthony noted, what about leap seconds?

I'm not advocating changing the behaviour of interval types. They're a
compromise for best functionality in the face of crazy and stupid
real-world rules.  I just wanted to point out that intervals are
inconsistent in all sorts of ways, and that there really isn't a good
answer.

--
Craig Ringer

This was supposed to go to the list. Sorry.