Why does SELECT '2019-05-03'::timestamp - '2018-05-07'::timestamp < '1 year 1 day'::INTERVAL; return FALSE in postgresql?

Question

I am trying to compare two dates and return TRUE if the first date is less than '1 year 1 day' from the second date.

Using 361 days instead of '1 year 1 day' returns FALSE, but this makes sense based on why justify_interval('360 days'::interval) results '1 year'.

But when I run SELECT '2019-05-03'::timestamp - '2018-05-07'::timestamp < '1 year 1 day'::INTERVAL; I get FALSE, and when I run

SELECT '2019-05-03'::timestamp - '1 year 1 day'::INTERVAL < '2018-05-07'::timestamp; I get TRUE.

Why do these return different things?

Answer 1

I can't find it in the docs, but this is due to the way intervals represented and compared.

Note that:

select timestamp '2019-05-03' - timestamp '2018-05-07' < interval '366 day';

gives you the expected result of TRUE.

To compare two intervals, Postgres first converts the intervals to integers. This is done in a pretty naive way, where years are concerned:

/*
 *      interval_relop  - is interval1 relop interval2
 *
 * Interval comparison is based on converting interval values to a linear
 * representation expressed in the units of the time field (microseconds,
 * in the case of integer timestamps) with days assumed to be always 24 hours
 * and months assumed to be always 30 days.  To avoid overflow, we need a
 * wider-than-int64 datatype for the linear representation, so use INT128.
 */

So, your query is asking:

select 361 * 24 * 3600 * 1000000 < (1 * 12 * 30 * 24 * 3600 * 1000000) + (1 * 24 * 3600 * 1000000);

Or,

select 31,190,400,000,000 < 31,190,400,000,000;

which is obviously false. ^^