2

Hello PostgreSQL experts.

I'm trying to understand why these 2 Boolean expressions return different results.

The first returns TRUE whereas the second returns FALSE.

SELECT CAST('2019-01-01T12:00:00' AS TIMESTAMP) - CAST('2018-01-01T13:00:00' AS TIMESTAMP) <= INTERVAL '365 DAYS', 
       CAST('2019-01-01T12:00:00' AS TIMESTAMP) - CAST('2018-01-01T13:00:00' AS TIMESTAMP) <= INTERVAL '1 YEAR';

Neither 2019 nor 2018 were leap years.

I expected that for non-leap years, a 1 year interval will be equivalent to a 365 day interval, but I'm obviously wrong.

Tested with PostgreSQL 15.

Your help will be highly appreciated!

Edit: So looks like this is more of a bug than it is a feature. "IEC/ISO 9075-2:2016 SQL foundations" defines 2 types of intervals. One is called year-month interval and the other day-time interval. Each type is comparable only with itself. Therefore, the 2nd predicate should have raised an error for incompatible types. This would have saved a lot of headaches for everyone who uses it. If there are PostgreSQL contributors reading this, I think this should be considered for implementation in a future release.

SQLRaptor
  • 671
  • 4
  • 14
  • `select '360 days'::interval = '1 year'::interval; t` from [Datetime](https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT) *... assuming 30 days per month ...*. `select '12 months'::interval = '1 year'::interval; t`. – Adrian Klaver Feb 11 '23 at 00:18

2 Answers2

4

Postgres appears to define an interval of 1 year as 365.25 days.

That would be because the interval type does not include a rooted start and end time. It's a size without position.

If you like, you can compare 1 metre and 1 metre from my chair.

So interval doesn't know which year you're talking about.

So it averages it to 365.25 days which is about what you'd get if you averaged most 4 year periods.

You can check this with:

select extract(epoch from interval '1 year')

This gives 31557600 and you can do the maths from there.

Edit: I got curious after some comments and discovered:

a) it's more complicated than I could possibly have imagined

b) it's a bit of a moving target.

This commit in April 2022 claims to undo a regression which (afaict) due to rounding made it 365 days not 365.25. It links to the commit which (apparently) introduced that, which was April 2021. The fix specifically mentions it relies on DAYS_PER_YEAR being a multiple of 0.25.

https://github.com/postgres/postgres/commit/f2a2bf66c87e14f07aefe23cbbe2f2d9edcd9734

The version of Postgres that I got 365.25 in is 14.6 SELECT VERSION() gives PostgreSQL 14.6 (Debian 14.6-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

So I'd guess my install is from before that rounding issue was introduced, and @Atmo has one from after that, and before this fix got released.

(All conclusions made here were from reading comments, not code).

Dates and times. Hard.

GregHNZ
  • 7,946
  • 1
  • 28
  • 30
  • 1
    This in violation of the SQL 2016 standard... – SQLRaptor Feb 11 '23 at 01:27
  • But this does not explain his observation, as 365.25 is even longer than 365, so his second inequality would certainly be true. The 31557600 seconds in 1 year is a peculiarity of "epoch", not of intervals generally. The peculiarity which explain his observation is the one of 30 day months, meaning 360 day years. – jjanes Feb 11 '23 at 03:37
  • Agreed. The part of size without position seems valid but on my DB at least, a year without positionning it next to a date, appears to be 360 days and `select extract(epoch from interval '1 year')` returns `31536000`, not `31557600` (365.00 days). – Atmo Feb 11 '23 at 05:57
  • Your edit explains why `SomeDate + interval '1 year'` gave us different result. Now, as was commented by @AdrianKlaver,`interval '1 year'` = `interval 360 days`, most likely because 1 year is exactly 12 months and for ease of calculation, 1 month (without precising which month we are talking about) was rounded to 30 days. That should make sense for everyone. The result is an easier to calculate (in contexts that mostly disappeared with old hardware) 360 days per year. I do not know what to think about it being modified by `extract (epoch from ...)`. – Atmo Feb 11 '23 at 07:03
  • However and importantly, when adding intervals to dates, `SomeDate + 'interval 1 year` = `SomeDate + interval '365 days'`, not accounting for leap years. – Atmo Feb 11 '23 at 07:06
1

The PostgresSQL documentation states (https://www.postgresql.org/docs/15/datatype-datetime.html#DATATYPE-INTERVAL-INPUT)

Internally interval values are stored as months, days, and microseconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a daylight savings time adjustment is involved.

So an interval INTERVAL '365 days' is stored as 0 months, 365 days, and 0 microseconds. An interval INTERVAL '1 year' is stored as 12 months, 0 days, and 0 microseconds.

When you compare two intervals each month is converted to 30 days and each day is converted to 24 hours. This leaves you with the strange result of an interval of 1 year being equivalent to an interval of 360 days (30 * 12), not 365 days.

postgres=# SELECT INTERVAL '360 days' = INTERVAL '1 year';
 ?column? 
----------
 t
(1 row)
Joe Koshakow
  • 26
  • 1
  • 4
  • If anyone is skeptical, here is the code that implements interval comparison in PostgreSQL: https://github.com/postgres/postgres/blob/d3a38318ac614f20a9e2e163bba083d15be54f06/src/backend/utils/adt/timestamp.c#L2387-L2489 – Joe Koshakow Aug 01 '23 at 12:26
  • The currently accepted answer is not quite correct. The 365.25 days per year constant is used for the `date_part` and `extract` functions, it is not used for interval comparisons. Therefore, the bug that was posted (https://github.com/postgres/postgres/commit/f2a2bf66c87e14f07aefe23cbbe2f2d9edcd9734), which I don't believe made it into any release, is not relevant to the question. – Joe Koshakow Aug 01 '23 at 12:36