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
