public inbox for [email protected]  

help / color / mirror / Atom feed
From: Aleksander Alekseev <[email protected]>
To: Joseph Koshakow <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Extract epoch from Interval weird behavior
Date: Thu, 24 Feb 2022 12:47:36 +0300
Message-ID: <CAJ7c6TMxMus4FvpzkwsROT7EwG3tk_Mn7yfPT-93h7PeSLWYzQ@mail.gmail.com> (raw)
In-Reply-To: <CAAvxfHcXEF+hD+xnw4BG6XaRjHWGZ0udY3Uao4n=zjhEgpuLLQ@mail.gmail.com>
References: <CAAvxfHd5n=13NYA2q_tUq=3=SuWU-CufmTf-Ozj=frEgt7pXwQ@mail.gmail.com>
	<CAAvxfHcXEF+hD+xnw4BG6XaRjHWGZ0udY3Uao4n=zjhEgpuLLQ@mail.gmail.com>

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


view thread (4+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Extract epoch from Interval weird behavior
  In-Reply-To: <CAJ7c6TMxMus4FvpzkwsROT7EwG3tk_Mn7yfPT-93h7PeSLWYzQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox