From: | Joseph Koshakow <koshy44(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Extract epoch from Interval weird behavior |
Date: | 2022-02-24 02:35:11 |
Message-ID: | CAAvxfHcXEF+hD+xnw4BG6XaRjHWGZ0udY3Uao4n=zjhEgpuLLQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Feb 23, 2022 at 7:42 PM Joseph Koshakow <koshy44(at)gmail(dot)com> wrote:
>
> Hi all,
>
> I noticed something odd when going through some
> of the Interval code. The DAYS_PER_YEAR constant
> is defined in src/include/datatype/timestamp.h.
> > #define DAYS_PER_YEAR 365.25 /* assumes leap year every four years */
>
> We execute the EXTRACT and date_part functions in
> src/backend/utils/adt/timestamp.c in
> > static Datum
> > interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
>
> When executing date_part we multiply the total
> years in the Interval by DAYS_PER_YEAR
> > result += ((double) DAYS_PER_YEAR * SECS_PER_DAY) * (interval->month / MONTHS_PER_YEAR);
> > result += ((double) DAYS_PER_MONTH * SECS_PER_DAY) * (interval->month % MONTHS_PER_YEAR);
> > result += ((double) SECS_PER_DAY) * interval->day;
>
>
> However when executing EXTRACT we first truncate
> DAYS_PER_YEAR to an integer, and then multiply it
> by the total years in the Interval
> /* this always fits into int64 */
> > secs_from_day_month = ((int64) DAYS_PER_YEAR * (interval->month / MONTHS_PER_YEAR) +
> > (int64) DAYS_PER_MONTH * (interval->month % MONTHS_PER_YEAR) +
> > interval->day) * SECS_PER_DAY;
>
> Is this truncation on purpose? It seems like
> EXTRACT is not accounting for leap years in
> it's calculation.
>
> - Joe Koshakow
Oops I sent that to the wrong email. If this isn't intented I've created a patch
that fixes it, with the following two open questions
* DAYS_PER_YEAR_NUM is recalculated every time. Is there anyway
to convert a float directly to a numeric to avoid this?
* For some reason the change adds a lot of trailing zeros to the result. I'm
not sure why that is.
- Joe Koshakow
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Consider-leap-years-when-extracting-epoch-from-inter.patch | text/x-patch | 4.3 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | osumi.takamichi@fujitsu.com | 2022-02-24 02:44:06 | RE: Optionally automatically disable logical replication subscriptions on error |
Previous Message | kuroda.hayato@fujitsu.com | 2022-02-24 02:34:55 | RE: [Proposal] Add foreign-server health checks infrastructure |