From: | Joseph Koshakow <koshy44(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Extract epoch from Interval weird behavior |
Date: | 2022-02-24 00:42:12 |
Message-ID: | CAAvxfHd5n=13NYA2q_tUq=3=SuWU-CufmTf-Ozj=frEgt7pXwQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Masahiko Sawada | 2022-02-24 01:32:54 | Re: Design of pg_stat_subscription_workers vs pgstats |
Previous Message | Jacob Champion | 2022-02-24 00:15:40 | [PATCH] Expose port->authn_id to extensions and triggers |