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
