Re: Extract epoch from Interval weird behavior

On Wed, Feb 23, 2022 at 7:42 PM Joseph Koshakow <koshy44@gmail.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