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

...and even worse:

SELECT ('1 year'::interval) = ('360 days'::interval); --> TRUE :-)
SELECT ('1 year'::interval) = ('365 days'::interval); --> FALSE :-)

On Tue, Aug 7, 2012 at 4:42 PM, Dmitry Koterov <dmitry@koterov.ru> 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

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.albe@wien.gv.at>wrote:

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.
---------- Forwarded message ----------
From: "Michael Trausch" <mike@trausch.us>
Date: Aug 8, 2012 10:12 AM
Subject: Re: [GENERAL] Interval "1 month" is equals to interval "30 days" -
WHY?
To: "Albe Laurenz" <laurenz.albe@wien.gv.at>

There is root in accounting for this type of view of the interval. In
accounting, a month is considered to have 30 days or 4.25 weeks, and a year
is considered to have 360 days. The reason for this is that both the month
and year are easier to work with when evenly divisible. A quarter then has
90 days (30 * 3 or 360 / 4), and certain other equalities can be held true.

If you need exact date math, be prepared to spend a *lot* of time on the
problem. All exact date math operations must have a starting point, and
"exact" has different meanings depending on the application. Good luck.
On Aug 8, 2012 5:55 AM, "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

BTW there are a much more short version of this:

CREATE OR REPLACE FUNCTION int_equal(interval, interval) RETURNS boolean
IMMUTABLE STRICT LANGUAGE sql AS
'SELECT $1::text = $2::text';

On Wed, Aug 8, 2012 at 4:51 PM, Albe Laurenz <laurenz.albe@wien.gv.at>wrote: