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