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

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