Re: Extract epoch from Interval weird behavior

Hi Joseph,

> > Is this truncation on purpose? It seems like
> > EXTRACT is not accounting for leap years in
> > it's calculation.

Extracting an epoch from an interval is quite a strange case since intervals are not connected to any specific dates.

For instance:

select extract('epoch' from interval '1 month')

.. returns 2592000 = 30*24*60*60. But what if the month is February? Should we account for the different number of days for intervals like 6 months or 24 months?

Also, leap years don't just happen every 4 years. Here is the actual logic:

bool is_leap_year(int Y) {
  if(Y % 400 == 0) return true;
  else if(Y % 100 == 0) return false;
  else if(Y % 4 == 0) return true;
  else return false;
}

And what about leap seconds?

All in all, I don't think that the benefit of the proposed change outweighs the fact that it will break the previous behavior for the users who may rely on it. I suggest keeping it simple, i.e. the way it is now. What I think we could do instead is explicitly document this behavior in [1].

[1]: https://www.postgresql.org/docs/current/functions-datetime.html

--
Best regards,
Aleksander Alekseev