Index: doc/src/sgml/func.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.267 diff -c -c -r1.267 func.sgml *** doc/src/sgml/func.sgml 18 Jul 2005 22:34:14 -0000 1.267 --- doc/src/sgml/func.sgml 20 Jul 2005 03:56:10 -0000 *************** *** 5145,5150 **** --- 5145,5166 ---- + justify_hours(interval) + interval + Adjust interval so 24-hour time periods are represented as days + justify_hours(interval '24 hours') + 1 day + + + + justify_days(interval) + interval + Adjust interval so 30-day time periods are represented as months + justify_days(interval '30 days') + 1 month + + + localtime time Time of day; see Index: src/backend/commands/variable.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/commands/variable.c,v retrieving revision 1.109 diff -c -c -r1.109 variable.c *** src/backend/commands/variable.c 28 Jun 2005 05:08:55 -0000 1.109 --- src/backend/commands/variable.c 20 Jul 2005 03:56:11 -0000 *************** *** 292,297 **** --- 292,306 ---- pfree(interval); return NULL; } + if (interval->day != 0) + { + if (source >= PGC_S_INTERACTIVE) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid interval value for time zone: day not allowed"))); + pfree(interval); + return NULL; + } if (doit) { /* Here we change from SQL to Unix sign convention */ *************** *** 414,419 **** --- 423,429 ---- Interval interval; interval.month = 0; + interval.day = 0; #ifdef HAVE_INT64_TIMESTAMP interval.time = -(CTimeZone * USECS_PER_SEC); #else Index: src/backend/utils/adt/date.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/date.c,v retrieving revision 1.112 diff -c -c -r1.112 date.c *** src/backend/utils/adt/date.c 12 Jul 2005 15:17:44 -0000 1.112 --- src/backend/utils/adt/date.c 20 Jul 2005 03:56:12 -0000 *************** *** 1423,1428 **** --- 1423,1429 ---- result = (Interval *) palloc(sizeof(Interval)); result->time = time; + result->day = 0; result->month = 0; PG_RETURN_INTERVAL_P(result); *************** *** 1477,1484 **** result = (Interval *) palloc(sizeof(Interval)); - result->time = (time1 - time2); result->month = 0; PG_RETURN_INTERVAL_P(result); } --- 1478,1486 ---- result = (Interval *) palloc(sizeof(Interval)); result->month = 0; + result->day = 0; + result->time = time1 - time2; PG_RETURN_INTERVAL_P(result); } Index: src/backend/utils/adt/formatting.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.90 diff -c -c -r1.90 formatting.c *** src/backend/utils/adt/formatting.c 24 Jun 2005 01:10:11 -0000 1.90 --- src/backend/utils/adt/formatting.c 20 Jul 2005 03:56:16 -0000 *************** *** 899,905 **** /* static int is_acdc(char *str, int *len); */ static int seq_search(char *name, char **array, int type, int max, int *len); static void do_to_timestamp(text *date_txt, text *fmt, ! struct pg_tm * tm, fsec_t *fsec); static char *fill_str(char *str, int c, int max); static FormatNode *NUM_cache(int len, NUMDesc *Num, char *pars_str, bool *shouldFree); static char *int_to_roman(int number); --- 899,905 ---- /* static int is_acdc(char *str, int *len); */ static int seq_search(char *name, char **array, int type, int max, int *len); static void do_to_timestamp(text *date_txt, text *fmt, ! struct pg_tm *tm, fsec_t *fsec); static char *fill_str(char *str, int c, int max); static FormatNode *NUM_cache(int len, NUMDesc *Num, char *pars_str, bool *shouldFree); static char *int_to_roman(int number); *************** *** 3028,3034 **** */ static void do_to_timestamp(text *date_txt, text *fmt, ! struct pg_tm * tm, fsec_t *fsec) { FormatNode *format; TmFromChar tmfc; --- 3028,3034 ---- */ static void do_to_timestamp(text *date_txt, text *fmt, ! struct pg_tm *tm, fsec_t *fsec) { FormatNode *format; TmFromChar tmfc; Index: src/backend/utils/adt/nabstime.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v retrieving revision 1.135 diff -c -c -r1.135 nabstime.c *** src/backend/utils/adt/nabstime.c 12 Jul 2005 16:04:56 -0000 1.135 --- src/backend/utils/adt/nabstime.c 20 Jul 2005 03:56:17 -0000 *************** *** 829,835 **** Interval *interval = PG_GETARG_INTERVAL_P(0); RelativeTime time; int year, ! month; #ifdef HAVE_INT64_TIMESTAMP int64 span; --- 829,836 ---- Interval *interval = PG_GETARG_INTERVAL_P(0); RelativeTime time; int year, ! month, ! day; #ifdef HAVE_INT64_TIMESTAMP int64 span; *************** *** 837,864 **** double span; #endif ! if (interval->month == 0) ! { ! year = 0; ! month = 0; ! } ! else if (abs(interval->month) >=12) ! { ! year = (interval->month / 12); ! month = (interval->month % 12); ! } ! else ! { ! year = 0; ! month = interval->month; ! } #ifdef HAVE_INT64_TIMESTAMP ! span = ((INT64CONST(365250000) * year + INT64CONST(30000000) * month) * ! INT64CONST(86400)) + interval->time; span /= USECS_PER_SEC; #else ! span = (365.25 * year + 30.0 * month) * SECS_PER_DAY + interval->time; #endif if (span < INT_MIN || span > INT_MAX) --- 838,854 ---- double span; #endif ! year = interval->month / 12; ! month = interval->month % 12; ! day = interval->day; #ifdef HAVE_INT64_TIMESTAMP ! span = ((INT64CONST(365250000) * year + INT64CONST(30000000) * month + ! INT64CONST(1000000) * day) * INT64CONST(86400)) + ! interval->time; span /= USECS_PER_SEC; #else ! span = (365.25 * year + 30.0 * month + day) * SECS_PER_DAY + interval->time; #endif if (span < INT_MIN || span > INT_MAX) *************** *** 876,882 **** RelativeTime reltime = PG_GETARG_RELATIVETIME(0); Interval *result; int year, ! month; result = (Interval *) palloc(sizeof(Interval)); --- 866,873 ---- RelativeTime reltime = PG_GETARG_RELATIVETIME(0); Interval *result; int year, ! month, ! day; result = (Interval *) palloc(sizeof(Interval)); *************** *** 887,892 **** --- 878,884 ---- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot convert reltime \"invalid\" to interval"))); result->time = 0; + result->day = 0; result->month = 0; break; *************** *** 896,910 **** --- 888,906 ---- reltime -= (year * (36525 * 864)); month = (reltime / (30 * SECS_PER_DAY)); reltime -= (month * (30 * SECS_PER_DAY)); + day = reltime / SECS_PER_DAY; + reltime -= day * SECS_PER_DAY; result->time = (reltime * USECS_PER_SEC); #else TMODULO(reltime, year, 36525 * 864); TMODULO(reltime, month, 30 * SECS_PER_DAY); + TMODULO(reltime, day, SECS_PER_DAY); result->time = reltime; #endif result->month = 12 * year + month; + result->day = day; break; } Index: src/backend/utils/adt/selfuncs.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/selfuncs.c,v retrieving revision 1.184 diff -c -c -r1.184 selfuncs.c *** src/backend/utils/adt/selfuncs.c 12 Jul 2005 16:04:57 -0000 1.184 --- src/backend/utils/adt/selfuncs.c 20 Jul 2005 03:56:20 -0000 *************** *** 2784,2793 **** * too accurate, but plenty good enough for our purposes. */ #ifdef HAVE_INT64_TIMESTAMP ! return (interval->time + (interval->month * ((365.25 / 12.0) * 86400000000.0))); #else ! return interval->time + ! interval ->month * (365.25 / 12.0 * 24.0 * 60.0 * 60.0); #endif } case RELTIMEOID: --- 2784,2794 ---- * too accurate, but plenty good enough for our purposes. */ #ifdef HAVE_INT64_TIMESTAMP ! return interval->time + interval->day * (double)USECS_PER_DAY + ! interval->month * ((365.25 / 12.0) * USECS_PER_DAY); #else ! return interval->time + interval->day * SECS_PER_DAY + ! interval->month * ((365.25 / 12.0) * (double)SECS_PER_DAY); #endif } case RELTIMEOID: Index: src/backend/utils/adt/timestamp.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.133 diff -c -c -r1.133 timestamp.c *** src/backend/utils/adt/timestamp.c 20 Jul 2005 03:50:24 -0000 1.133 --- src/backend/utils/adt/timestamp.c 20 Jul 2005 03:56:22 -0000 *************** *** 596,601 **** --- 596,602 ---- #else interval->time = pq_getmsgfloat8(buf); #endif + interval->day = pq_getmsgint(buf, sizeof(interval->day)); interval->month = pq_getmsgint(buf, sizeof(interval->month)); AdjustIntervalForTypmod(interval, typmod); *************** *** 618,623 **** --- 619,625 ---- #else pq_sendfloat8(&buf, interval->time); #endif + pq_sendint(&buf, interval->day, sizeof(interval->day)); pq_sendint(&buf, interval->month, sizeof(interval->month)); PG_RETURN_BYTEA_P(pq_endtypsend(&buf)); } *************** *** 697,744 **** else if (range == INTERVAL_MASK(YEAR)) { interval->month = (interval->month / 12) * 12; interval->time = 0; } else if (range == INTERVAL_MASK(MONTH)) { interval->month %= 12; interval->time = 0; } /* YEAR TO MONTH */ else if (range == (INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH))) interval->time = 0; ! else if (range == INTERVAL_MASK(DAY)) { interval->month = 0; ! ! #ifdef HAVE_INT64_TIMESTAMP ! interval->time = ((int) (interval->time / USECS_PER_DAY)) * ! USECS_PER_DAY; ! ! #else ! interval->time = ((int) (interval->time / SECS_PER_DAY)) * SECS_PER_DAY; ! #endif } else if (range == INTERVAL_MASK(HOUR)) { - #ifdef HAVE_INT64_TIMESTAMP - int64 day; - #else - double day; - #endif - interval->month = 0; #ifdef HAVE_INT64_TIMESTAMP - day = interval->time / USECS_PER_DAY; - interval->time -= day * USECS_PER_DAY; interval->time = (interval->time / USECS_PER_HOUR) * USECS_PER_HOUR; - #else ! TMODULO(interval->time, day, (double)SECS_PER_DAY); ! interval->time = ((int) (interval->time / 3600)) * 3600.0; #endif } else if (range == INTERVAL_MASK(MINUTE)) --- 699,735 ---- else if (range == INTERVAL_MASK(YEAR)) { interval->month = (interval->month / 12) * 12; + interval->day = 0; interval->time = 0; } else if (range == INTERVAL_MASK(MONTH)) { interval->month %= 12; + interval->day = 0; interval->time = 0; } /* YEAR TO MONTH */ else if (range == (INTERVAL_MASK(YEAR) | INTERVAL_MASK(MONTH))) + { + /* month is already year to month */ + interval->day = 0; interval->time = 0; ! } else if (range == INTERVAL_MASK(DAY)) { interval->month = 0; ! interval->time = 0; } else if (range == INTERVAL_MASK(HOUR)) { interval->month = 0; + interval->day = 0; #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_HOUR) * USECS_PER_HOUR; #else ! interval->time = ((int)(interval->time / 3600)) * 3600.0; #endif } else if (range == INTERVAL_MASK(MINUTE)) *************** *** 750,755 **** --- 741,747 ---- #endif interval->month = 0; + interval->day = 0; #ifdef HAVE_INT64_TIMESTAMP hour = interval->time / USECS_PER_HOUR; *************** *** 759,785 **** #else TMODULO(interval->time, hour, 3600.0); ! interval->time = ((int) (interval->time / 60)) * 60; #endif } else if (range == INTERVAL_MASK(SECOND)) { #ifdef HAVE_INT64_TIMESTAMP int64 minute; - #else double minute; #endif interval->month = 0; #ifdef HAVE_INT64_TIMESTAMP minute = interval->time / USECS_PER_MINUTE; interval->time -= minute * USECS_PER_MINUTE; - #else TMODULO(interval->time, minute, 60.0); ! /* interval->time = (int)(interval->time); */ #endif } /* DAY TO HOUR */ --- 751,776 ---- #else TMODULO(interval->time, hour, 3600.0); ! interval->time = ((int)(interval->time / 60)) * 60.0; #endif } else if (range == INTERVAL_MASK(SECOND)) { #ifdef HAVE_INT64_TIMESTAMP int64 minute; #else double minute; #endif interval->month = 0; + interval->day = 0; #ifdef HAVE_INT64_TIMESTAMP minute = interval->time / USECS_PER_MINUTE; interval->time -= minute * USECS_PER_MINUTE; #else TMODULO(interval->time, minute, 60.0); ! /* return subseconds too */ #endif } /* DAY TO HOUR */ *************** *** 791,799 **** #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_HOUR) * USECS_PER_HOUR; - #else ! interval->time = ((int) (interval->time / 3600)) * 3600; #endif } /* DAY TO MINUTE */ --- 782,789 ---- #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_HOUR) * USECS_PER_HOUR; #else ! interval->time = ((int) (interval->time / 3600)) * 3600.0; #endif } /* DAY TO MINUTE */ *************** *** 806,814 **** #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_MINUTE) * USECS_PER_MINUTE; - #else ! interval->time = ((int) (interval->time / 60)) * 60; #endif } /* DAY TO SECOND */ --- 796,803 ---- #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_MINUTE) * USECS_PER_MINUTE; #else ! interval->time = ((int)(interval->time / 60)) * 60.0; #endif } /* DAY TO SECOND */ *************** *** 822,845 **** else if (range == (INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE))) { - #ifdef HAVE_INT64_TIMESTAMP - int64 day; - - #else - double day; - #endif - interval->month = 0; #ifdef HAVE_INT64_TIMESTAMP - day = (interval->time / USECS_PER_DAY); - interval->time -= day * USECS_PER_DAY; interval->time = (interval->time / USECS_PER_MINUTE) * USECS_PER_MINUTE; - #else ! TMODULO(interval->time, day, (double)SECS_PER_DAY); ! interval->time = ((int) (interval->time / 60)) * 60; #endif } /* HOUR TO SECOND */ --- 811,824 ---- else if (range == (INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE))) { interval->month = 0; + interval->day = 0; #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_MINUTE) * USECS_PER_MINUTE; #else ! interval->time = ((int)(interval->time / 60)) * 60.0; #endif } /* HOUR TO SECOND */ *************** *** 847,868 **** INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND))) { - #ifdef HAVE_INT64_TIMESTAMP - int64 day; - - #else - double day; - #endif - interval->month = 0; ! ! #ifdef HAVE_INT64_TIMESTAMP ! day = interval->time / USECS_PER_DAY; ! interval->time -= day * USECS_PER_DAY; ! ! #else ! TMODULO(interval->time, day, (double)SECS_PER_DAY); ! #endif } /* MINUTE TO SECOND */ else if (range == (INTERVAL_MASK(MINUTE) | --- 826,834 ---- INTERVAL_MASK(MINUTE) | INTERVAL_MASK(SECOND))) { interval->month = 0; ! interval->day = 0; ! /* return subseconds too */ } /* MINUTE TO SECOND */ else if (range == (INTERVAL_MASK(MINUTE) | *************** *** 876,881 **** --- 842,848 ---- #endif interval->month = 0; + interval->day = 0; #ifdef HAVE_INT64_TIMESTAMP hour = interval->time / USECS_PER_HOUR; *************** *** 1029,1035 **** * timezone) will be used. */ int ! timestamp2tm(Timestamp dt, int *tzp, struct pg_tm * tm, fsec_t *fsec, char **tzn, pg_tz *attimezone) { Timestamp date; Timestamp time; --- 996,1002 ---- * timezone) will be used. */ int ! timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm, fsec_t *fsec, char **tzn, pg_tz *attimezone) { Timestamp date; Timestamp time; *************** *** 1165,1171 **** * Returns -1 on failure (value out of range). */ int ! tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result) { #ifdef HAVE_INT64_TIMESTAMP int date; --- 1132,1138 ---- * Returns -1 on failure (value out of range). */ int ! tm2timestamp(struct pg_tm *tm, fsec_t fsec, int *tzp, Timestamp *result) { #ifdef HAVE_INT64_TIMESTAMP int date; *************** *** 1205,1211 **** * Convert a interval data type to a tm structure. */ int ! interval2tm(Interval span, struct pg_tm * tm, fsec_t *fsec) { #ifdef HAVE_INT64_TIMESTAMP int64 time; --- 1172,1178 ---- * Convert a interval data type to a tm structure. */ int ! interval2tm(Interval span, struct pg_tm *tm, fsec_t *fsec) { #ifdef HAVE_INT64_TIMESTAMP int64 time; *************** *** 1213,1235 **** double time; #endif ! if (span.month != 0) ! { ! tm->tm_year = span.month / 12; ! tm->tm_mon = span.month % 12; ! ! } ! else ! { ! tm->tm_year = 0; ! tm->tm_mon = 0; ! } ! time = span.time; #ifdef HAVE_INT64_TIMESTAMP - tm->tm_mday = (time / USECS_PER_DAY); - time -= (tm->tm_mday * USECS_PER_DAY); tm->tm_hour = (time / USECS_PER_HOUR); time -= (tm->tm_hour * USECS_PER_HOUR); tm->tm_min = (time / USECS_PER_MINUTE); --- 1180,1191 ---- double time; #endif ! tm->tm_year = span.month / 12; ! tm->tm_mon = span.month % 12; ! tm->tm_mday = span.day; time = span.time; #ifdef HAVE_INT64_TIMESTAMP tm->tm_hour = (time / USECS_PER_HOUR); time -= (tm->tm_hour * USECS_PER_HOUR); tm->tm_min = (time / USECS_PER_MINUTE); *************** *** 1237,1243 **** tm->tm_sec = (time / USECS_PER_SEC); *fsec = (time - (tm->tm_sec * USECS_PER_SEC)); #else - TMODULO(time, tm->tm_mday, (double)SECS_PER_DAY); TMODULO(time, tm->tm_hour, 3600.0); TMODULO(time, tm->tm_min, 60.0); TMODULO(time, tm->tm_sec, 1.0); --- 1193,1198 ---- *************** *** 1248,1264 **** } int ! tm2interval(struct pg_tm * tm, fsec_t fsec, Interval *span) { span->month = tm->tm_year * 12 + tm->tm_mon; #ifdef HAVE_INT64_TIMESTAMP ! span->time = (((((((tm->tm_mday * INT64CONST(24)) + ! tm->tm_hour) * INT64CONST(60)) + tm->tm_min) * INT64CONST(60)) + tm->tm_sec) * USECS_PER_SEC) + fsec; #else ! span->time = (((((tm->tm_mday * 24.0) + ! tm->tm_hour) * 60.0) + tm->tm_min) * 60.0) + tm->tm_sec; span->time = JROUND(span->time + fsec); --- 1203,1218 ---- } int ! tm2interval(struct pg_tm *tm, fsec_t fsec, Interval *span) { span->month = tm->tm_year * 12 + tm->tm_mon; + span->day = tm->tm_mday; #ifdef HAVE_INT64_TIMESTAMP ! span->time = (((((tm->tm_hour * INT64CONST(60)) + tm->tm_min) * INT64CONST(60)) + tm->tm_sec) * USECS_PER_SEC) + fsec; #else ! span->time = (((tm->tm_hour * 60.0) + tm->tm_min) * 60.0) + tm->tm_sec; span->time = JROUND(span->time + fsec); *************** *** 1320,1326 **** *---------------------------------------------------------*/ void ! GetEpochTime(struct pg_tm * tm) { struct pg_tm *t0; pg_time_t epoch = 0; --- 1274,1280 ---- *---------------------------------------------------------*/ void ! GetEpochTime(struct pg_tm *tm) { struct pg_tm *t0; pg_time_t epoch = 0; *************** *** 1654,1668 **** span2 = interval2->time; #ifdef HAVE_INT64_TIMESTAMP ! if (interval1->month != 0) ! span1 += interval1->month * INT64CONST(30) * USECS_PER_DAY; ! if (interval2->month != 0) ! span2 += interval2->month * INT64CONST(30) * USECS_PER_DAY; ! #else ! if (interval1->month != 0) ! span1 += interval1->month * (30.0 * SECS_PER_DAY); ! if (interval2->month != 0) ! span2 += interval2->month * (30.0 * SECS_PER_DAY); #endif return ((span1 < span2) ? -1 : (span1 > span2) ? 1 : 0); --- 1608,1622 ---- span2 = interval2->time; #ifdef HAVE_INT64_TIMESTAMP ! span1 += interval1->month * INT64CONST(30) * USECS_PER_DAY; ! span1 += interval1->day * INT64CONST(24) * USECS_PER_HOUR; ! span2 += interval2->month * INT64CONST(30) * USECS_PER_DAY; ! span2 += interval2->day * INT64CONST(24) * USECS_PER_HOUR; ! #else ! span1 += interval1->month * (30.0 * SECS_PER_DAY); ! span1 += interval1->day * (24.0 * SECS_PER_HOUR); ! span2 += interval2->month * (30.0 * SECS_PER_DAY); ! span2 += interval2->day * (24.0 * SECS_PER_HOUR); #endif return ((span1 < span2) ? -1 : (span1 > span2) ? 1 : 0); *************** *** 1744,1750 **** * sizeof(Interval), so that any garbage pad bytes in the structure * won't be included in the hash! */ ! return hash_any((unsigned char *) key, sizeof(key->time) + sizeof(key->month)); } /* overlaps_timestamp() --- implements the SQL92 OVERLAPS operator. --- 1698,1705 ---- * sizeof(Interval), so that any garbage pad bytes in the structure * won't be included in the hash! */ ! return hash_any((unsigned char *) key, ! sizeof(key->time) + sizeof(key->day) + sizeof(key->month)); } /* overlaps_timestamp() --- implements the SQL92 OVERLAPS operator. *************** *** 1934,1951 **** #endif result->month = 0; PG_RETURN_INTERVAL_P(result); } /* timestamp_pl_interval() * Add a interval to a timestamp data type. ! * Note that interval has provisions for qualitative year/month * units, so try to do the right thing with them. * To add a month, increment the month, and use the same day of month. * Then, if the next month has fewer days, set the day of month * to the last day of month. * Lastly, add in the "quantitative time". */ Datum --- 1889,1964 ---- #endif result->month = 0; + result->day = 0; + + result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours, + IntervalPGetDatum(result))); + PG_RETURN_INTERVAL_P(result); + } + + /* interval_justify_hours() + * Adjust interval so 'time' contains less than a whole day, and + * 'day' contains an integral number of days. This is useful for + * situations (such as non-TZ) where '1 day' = '24 hours' is valid, + * e.g. interval subtraction and division. The SQL standard requires + * such conversion in these cases, but not the conversion of days to months. + */ + Datum + interval_justify_hours(PG_FUNCTION_ARGS) + { + Interval *span = PG_GETARG_INTERVAL_P(0); + Interval *result; + + result = (Interval *) palloc(sizeof(Interval)); + result->month = span->month; + result->time = span->time; + + #ifdef HAVE_INT64_TIMESTAMP + result->time += span->day * USECS_PER_DAY; + result->day = result->time / USECS_PER_DAY; + result->time -= result->day * USECS_PER_DAY; + #else + result->time += span->day * (double)SECS_PER_DAY; + TMODULO(result->time, result->day, (double)SECS_PER_DAY); + #endif PG_RETURN_INTERVAL_P(result); } + /* interval_justify_days() + * Adjust interval so 'time' contains less than 30 days, and + * adds as months. + */ + Datum + interval_justify_days(PG_FUNCTION_ARGS) + { + Interval *span = PG_GETARG_INTERVAL_P(0); + Interval *result; + + result = (Interval *) palloc(sizeof(Interval)); + result->day = span->day; + result->time = span->time; + + #ifdef HAVE_INT64_TIMESTAMP + result->day += span->month * 30.0; + result->month = span->day / 30; + result->day -= result->month * 30; + #else + result->day += span->month * 30.0; + TMODULO(result->day, result->month, 30.0); + #endif + + PG_RETURN_INTERVAL_P(result); + } /* timestamp_pl_interval() * Add a interval to a timestamp data type. ! * Note that interval has provisions for qualitative year/month and day * units, so try to do the right thing with them. * To add a month, increment the month, and use the same day of month. * Then, if the next month has fewer days, set the day of month * to the last day of month. + * To add a day, increment the mday, and use the same time of day. * Lastly, add in the "quantitative time". */ Datum *************** *** 1957,1963 **** if (TIMESTAMP_NOT_FINITE(timestamp)) result = timestamp; - else { if (span->month != 0) --- 1970,1975 ---- *************** *** 1993,1999 **** errmsg("timestamp out of range"))); } ! timestamp +=span->time; result = timestamp; } --- 2005,2033 ---- errmsg("timestamp out of range"))); } ! if (span->day != 0) ! { ! struct pg_tm tt, ! *tm = &tt; ! fsec_t fsec; ! int julian; ! ! if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) !=0) ! ereport(ERROR, ! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), ! errmsg("timestamp out of range"))); ! ! /* Add days by converting to and from julian */ ! julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day; ! j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday); ! ! if (tm2timestamp(tm, fsec, NULL, ×tamp) !=0) ! ereport(ERROR, ! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), ! errmsg("timestamp out of range"))); ! } ! ! timestamp += span->time; result = timestamp; } *************** *** 2008,2013 **** --- 2042,2048 ---- Interval tspan; tspan.month = -span->month; + tspan.day = -span->day; tspan.time = -span->time; return DirectFunctionCall2(timestamp_pl_interval, *************** *** 2036,2042 **** if (TIMESTAMP_NOT_FINITE(timestamp)) result = timestamp; - else { if (span->month != 0) --- 2071,2076 ---- *************** *** 2074,2080 **** errmsg("timestamp out of range"))); } ! timestamp +=span->time; result = timestamp; } --- 2108,2138 ---- errmsg("timestamp out of range"))); } ! if (span->day != 0) ! { ! struct pg_tm tt, ! *tm = &tt; ! fsec_t fsec; ! int julian; ! ! if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) !=0) ! ereport(ERROR, ! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), ! errmsg("timestamp out of range"))); ! ! /* Add days by converting to and from julian */ ! julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day; ! j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday); ! ! tz = DetermineLocalTimeZone(tm); ! ! if (tm2timestamp(tm, fsec, &tz, ×tamp) !=0) ! ereport(ERROR, ! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), ! errmsg("timestamp out of range"))); ! } ! ! timestamp += span->time; result = timestamp; } *************** *** 2089,2094 **** --- 2147,2153 ---- Interval tspan; tspan.month = -span->month; + tspan.day = -span->day; tspan.time = -span->time; return DirectFunctionCall2(timestamptz_pl_interval, *************** *** 2106,2111 **** --- 2165,2171 ---- result = (Interval *) palloc(sizeof(Interval)); result->time = -(interval->time); + result->day = -(interval->day); result->month = -(interval->month); PG_RETURN_INTERVAL_P(result); *************** *** 2151,2156 **** --- 2211,2217 ---- result = (Interval *) palloc(sizeof(Interval)); result->month = (span1->month + span2->month); + result->day = (span1->day + span2->day); #ifdef HAVE_INT64_TIMESTAMP result->time = (span1->time + span2->time); #else *************** *** 2170,2175 **** --- 2231,2237 ---- result = (Interval *) palloc(sizeof(Interval)); result->month = (span1->month - span2->month); + result->day = (span1->day - span2->day); #ifdef HAVE_INT64_TIMESTAMP result->time = (span1->time - span2->time); #else *************** *** 2188,2210 **** #ifdef HAVE_INT64_TIMESTAMP int64 months; #else double months; #endif result = (Interval *) palloc(sizeof(Interval)); months = (span1->month * factor); #ifdef HAVE_INT64_TIMESTAMP result->month = months; result->time = (span1->time * factor); ! result->time += (months - result->month) * INT64CONST(30) * ! USECS_PER_DAY; #else result->month = (int)months; result->time = JROUND(span1->time * factor); /* evaluate fractional months as 30 days */ result->time += JROUND((months - result->month) * 30 * SECS_PER_DAY); #endif PG_RETURN_INTERVAL_P(result); --- 2250,2279 ---- #ifdef HAVE_INT64_TIMESTAMP int64 months; + int64 days; #else double months; + double days; #endif result = (Interval *) palloc(sizeof(Interval)); months = (span1->month * factor); + days = (span1->day * factor); #ifdef HAVE_INT64_TIMESTAMP result->month = months; + result->day = days; result->time = (span1->time * factor); ! result->time += (months - result->month) * INT64CONST(30) * USECS_PER_DAY; ! result->time += (days - result->day) * INT64CONST(24) * USECS_PER_HOUR; #else result->month = (int)months; + result->day = (int)days; result->time = JROUND(span1->time * factor); /* evaluate fractional months as 30 days */ result->time += JROUND((months - result->month) * 30 * SECS_PER_DAY); + /* evaluate fractional days as 24 hours */ + result->time += JROUND((days - result->day) * 24 * SECS_PER_HOUR); #endif PG_RETURN_INTERVAL_P(result); *************** *** 2225,2236 **** { Interval *span = PG_GETARG_INTERVAL_P(0); float8 factor = PG_GETARG_FLOAT8(1); Interval *result; - #ifndef HAVE_INT64_TIMESTAMP - double months; - #endif - result = (Interval *) palloc(sizeof(Interval)); if (factor == 0.0) --- 2294,2302 ---- { Interval *span = PG_GETARG_INTERVAL_P(0); float8 factor = PG_GETARG_FLOAT8(1); + double month_remainder, day_remainder; Interval *result; result = (Interval *) palloc(sizeof(Interval)); if (factor == 0.0) *************** *** 2238,2257 **** (errcode(ERRCODE_DIVISION_BY_ZERO), errmsg("division by zero"))); #ifdef HAVE_INT64_TIMESTAMP ! result->month = (span->month / factor); ! result->time = (span->time / factor); ! /* evaluate fractional months as 30 days */ ! result->time += ((span->month - (result->month * factor)) * ! INT64CONST(30) * USECS_PER_DAY) / factor; #else ! months = span->month / factor; ! result->month = (int)months; ! result->time = JROUND(span->time / factor); ! /* evaluate fractional months as 30 days */ ! result->time += JROUND((months - result->month) * 30 * SECS_PER_DAY); #endif PG_RETURN_INTERVAL_P(result); } --- 2304,2332 ---- (errcode(ERRCODE_DIVISION_BY_ZERO), errmsg("division by zero"))); + result->month = span->month / factor; + result->day = span->day / factor; + result->time = span->time / factor; + + /* Computer remainders */ + month_remainder = (span->month - result->month * factor) / factor; + day_remainder = (span->day - result->day * factor) / factor; + + /* Cascade fractions to lower units */ + /* fractional months full days into days */ + result->day += month_remainder * 30; + /* fractional months partial days into time */ + day_remainder += (month_remainder * 30) - (int)(month_remainder * 30); + #ifdef HAVE_INT64_TIMESTAMP ! result->time += day_remainder * USECS_PER_DAY; #else ! result->time += day_remainder * SECS_PER_DAY; ! result->time = JROUND(result->time); #endif + result = DatumGetIntervalP(DirectFunctionCall1(interval_justify_hours, + IntervalPGetDatum(result))); PG_RETURN_INTERVAL_P(result); } *************** *** 2276,2284 **** Interval *newsum; ArrayType *result; - /* We assume the input is array of interval */ deconstruct_array(transarray, ! INTERVALOID, 12, false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); --- 2351,2358 ---- Interval *newsum; ArrayType *result; deconstruct_array(transarray, ! INTERVALOID, sizeof(Interval), false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); *************** *** 2304,2310 **** transdatums[1] = IntervalPGetDatum(&N); result = construct_array(transdatums, 2, ! INTERVALOID, 12, false, 'd'); PG_RETURN_ARRAYTYPE_P(result); } --- 2378,2384 ---- transdatums[1] = IntervalPGetDatum(&N); result = construct_array(transdatums, 2, ! INTERVALOID, sizeof(Interval), false, 'd'); PG_RETURN_ARRAYTYPE_P(result); } *************** *** 2318,2326 **** Interval sumX, N; - /* We assume the input is array of interval */ deconstruct_array(transarray, ! INTERVALOID, 12, false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); --- 2392,2399 ---- Interval sumX, N; deconstruct_array(transarray, ! INTERVALOID, sizeof(Interval), false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); *************** *** 2721,2727 **** result = palloc(len); VARATT_SIZEP(result) = len; ! memmove(VARDATA(result), str, (len - VARHDRSZ)); pfree(str); --- 2794,2800 ---- result = palloc(len); VARATT_SIZEP(result) = len; ! memmove(VARDATA(result), str, len - VARHDRSZ); pfree(str); *************** *** 3080,3085 **** --- 3153,3159 ---- { switch (val) { + /* fall through */ case DTK_MILLENNIUM: /* caution: C division may have negative remainder */ tm->tm_year = (tm->tm_year / 1000) * 1000; *************** *** 3830,3840 **** #else result = interval->time; #endif ! if (interval->month != 0) ! { ! result += (365.25 * SECS_PER_DAY) * (interval->month / 12); ! result += (30.0 * SECS_PER_DAY) * (interval->month % 12); ! } } else { --- 3904,3912 ---- #else result = interval->time; #endif ! result += (365.25 * SECS_PER_DAY) * (interval->month / 12); ! result += (30.0 * SECS_PER_DAY) * (interval->month % 12); ! result += interval->day * SECS_PER_DAY; } else { Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.376 diff -c -c -r1.376 pg_proc.h *** src/include/catalog/pg_proc.h 10 Jul 2005 21:13:59 -0000 1.376 --- src/include/catalog/pg_proc.h 20 Jul 2005 03:56:27 -0000 *************** *** 1497,1502 **** --- 1497,1506 ---- DESCR("convert abstime to timestamp with time zone"); DATA(insert OID = 1174 ( timestamptz PGNSP PGUID 12 f f t f s 1 1184 "1082" _null_ _null_ _null_ date_timestamptz - _null_ )); DESCR("convert date to timestamp with time zone"); + DATA(insert OID = 1175 ( justify_hours PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_ interval_justify_hours - _null_ )); + DESCR("promote groups of 24 hours to numbers of days"); + DATA(insert OID = 1295 ( justify_days PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_ interval_justify_days - _null_ )); + DESCR("promote groups of 30 days to numbers of months"); DATA(insert OID = 1176 ( timestamptz PGNSP PGUID 14 f f t f s 2 1184 "1082 1083" _null_ _null_ _null_ "select cast(($1 + $2) as timestamp with time zone)" - _null_ )); DESCR("convert date and time to timestamp with time zone"); DATA(insert OID = 1177 ( interval PGNSP PGUID 12 f f t f i 1 1186 "703" _null_ _null_ _null_ reltime_interval - _null_ )); Index: src/include/catalog/pg_type.h =================================================================== RCS file: /cvsroot/pgsql/src/include/catalog/pg_type.h,v retrieving revision 1.163 diff -c -c -r1.163 pg_type.h *** src/include/catalog/pg_type.h 7 Jul 2005 20:39:59 -0000 1.163 --- src/include/catalog/pg_type.h 20 Jul 2005 03:56:28 -0000 *************** *** 457,463 **** DESCR("date and time with time zone"); #define TIMESTAMPTZOID 1184 DATA(insert OID = 1185 ( _timestamptz PGNSP PGUID -1 f b t \054 0 1184 array_in array_out array_recv array_send - d x f 0 -1 0 _null_ _null_ )); ! DATA(insert OID = 1186 ( interval PGNSP PGUID 12 f b t \054 0 0 interval_in interval_out interval_recv interval_send - d p f 0 -1 0 _null_ _null_ )); DESCR("@ , time interval"); #define INTERVALOID 1186 DATA(insert OID = 1187 ( _interval PGNSP PGUID -1 f b t \054 0 1186 array_in array_out array_recv array_send - d x f 0 -1 0 _null_ _null_ )); --- 457,463 ---- DESCR("date and time with time zone"); #define TIMESTAMPTZOID 1184 DATA(insert OID = 1185 ( _timestamptz PGNSP PGUID -1 f b t \054 0 1184 array_in array_out array_recv array_send - d x f 0 -1 0 _null_ _null_ )); ! DATA(insert OID = 1186 ( interval PGNSP PGUID 16 f b t \054 0 0 interval_in interval_out interval_recv interval_send - d p f 0 -1 0 _null_ _null_ )); DESCR("@ , time interval"); #define INTERVALOID 1186 DATA(insert OID = 1187 ( _interval PGNSP PGUID -1 f b t \054 0 1186 array_in array_out array_recv array_send - d x f 0 -1 0 _null_ _null_ )); Index: src/include/utils/timestamp.h =================================================================== RCS file: /cvsroot/pgsql/src/include/utils/timestamp.h,v retrieving revision 1.46 diff -c -c -r1.46 timestamp.h *** src/include/utils/timestamp.h 29 Jun 2005 22:51:57 -0000 1.46 --- src/include/utils/timestamp.h 20 Jul 2005 03:56:28 -0000 *************** *** 25,33 **** /* * Timestamp represents absolute time. ! * Interval represents delta time. Keep track of months (and years) ! * separately since the elapsed time spanned is unknown until instantiated ! * relative to an absolute time. * * Note that Postgres uses "time interval" to mean a bounded interval, * consisting of a beginning and ending time, not a time span - thomas 97/03/20 --- 25,33 ---- /* * Timestamp represents absolute time. ! * Interval represents delta time. Keep track of months (and years), days, ! * and time separately since the elapsed time spanned is unknown until ! * instantiated relative to an absolute time. * * Note that Postgres uses "time interval" to mean a bounded interval, * consisting of a beginning and ending time, not a time span - thomas 97/03/20 *************** *** 45,56 **** typedef struct { #ifdef HAVE_INT64_TIMESTAMP ! int64 time; /* all time units other than months and ! * years */ #else ! double time; /* all time units other than months and ! * years */ #endif int32 month; /* months and years, after time for * alignment */ } Interval; --- 45,57 ---- typedef struct { #ifdef HAVE_INT64_TIMESTAMP ! int64 time; /* all time units other than days, ! * months and years */ #else ! double time; /* all time units other than days, ! * months and years */ #endif + int32 day; /* days, after time for alignment */ int32 month; /* months and years, after time for * alignment */ } Interval; *************** *** 60,65 **** --- 61,67 ---- #define MAX_INTERVAL_PRECISION 6 #define SECS_PER_DAY 86400 + #define SECS_PER_HOUR 3600 #ifdef HAVE_INT64_TIMESTAMP #define USECS_PER_DAY INT64CONST(86400000000) #define USECS_PER_HOUR INT64CONST(3600000000) *************** *** 212,217 **** --- 214,221 ---- extern Datum interval_hash(PG_FUNCTION_ARGS); extern Datum interval_smaller(PG_FUNCTION_ARGS); extern Datum interval_larger(PG_FUNCTION_ARGS); + extern Datum interval_justify_hours(PG_FUNCTION_ARGS); + extern Datum interval_justify_days(PG_FUNCTION_ARGS); extern Datum timestamp_text(PG_FUNCTION_ARGS); extern Datum text_timestamp(PG_FUNCTION_ARGS); *************** *** 266,281 **** extern TimestampTz GetCurrentTimestamp(void); ! extern int tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *dt); ! extern int timestamp2tm(Timestamp dt, int *tzp, struct pg_tm * tm, fsec_t *fsec, char **tzn, pg_tz *attimezone); extern void dt2time(Timestamp dt, int *hour, int *min, int *sec, fsec_t *fsec); ! extern int interval2tm(Interval span, struct pg_tm * tm, fsec_t *fsec); ! extern int tm2interval(struct pg_tm * tm, fsec_t fsec, Interval *span); extern Timestamp SetEpochTimestamp(void); ! extern void GetEpochTime(struct pg_tm * tm); extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2); --- 270,285 ---- extern TimestampTz GetCurrentTimestamp(void); ! extern int tm2timestamp(struct pg_tm *tm, fsec_t fsec, int *tzp, Timestamp *dt); ! extern int timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm, fsec_t *fsec, char **tzn, pg_tz *attimezone); extern void dt2time(Timestamp dt, int *hour, int *min, int *sec, fsec_t *fsec); ! extern int interval2tm(Interval span, struct pg_tm *tm, fsec_t *fsec); ! extern int tm2interval(struct pg_tm *tm, fsec_t fsec, Interval *span); extern Timestamp SetEpochTimestamp(void); ! extern void GetEpochTime(struct pg_tm *tm); extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2); Index: src/interfaces/ecpg/pgtypeslib/interval.c =================================================================== RCS file: /cvsroot/pgsql/src/interfaces/ecpg/pgtypeslib/interval.c,v retrieving revision 1.23 diff -c -c -r1.23 interval.c *** src/interfaces/ecpg/pgtypeslib/interval.c 12 Jul 2005 16:05:12 -0000 1.23 --- src/interfaces/ecpg/pgtypeslib/interval.c 20 Jul 2005 03:56:29 -0000 *************** *** 33,39 **** * can be used to represent time spans. */ static int ! DecodeTime(char *str, int fmask, int *tmask, struct tm * tm, fsec_t *fsec) { char *cp; --- 33,39 ---- * can be used to represent time spans. */ static int ! DecodeTime(char *str, int fmask, int *tmask, struct tm *tm, fsec_t *fsec) { char *cp; *************** *** 107,113 **** * preceding an hh:mm:ss field. - thomas 1998-04-30 */ int ! DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fsec_t *fsec) { int is_before = FALSE; --- 107,113 ---- * preceding an hh:mm:ss field. - thomas 1998-04-30 */ int ! DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm *tm, fsec_t *fsec) { int is_before = FALSE; *************** *** 445,451 **** * - thomas 1998-04-30 */ int ! EncodeInterval(struct tm * tm, fsec_t fsec, int style, char *str) { int is_before = FALSE; int is_nonzero = FALSE; --- 445,451 ---- * - thomas 1998-04-30 */ int ! EncodeInterval(struct tm *tm, fsec_t fsec, int style, char *str) { int is_before = FALSE; int is_nonzero = FALSE; *************** *** 670,676 **** * Convert a interval data type to a tm structure. */ static int ! interval2tm(interval span, struct tm * tm, fsec_t *fsec) { #ifdef HAVE_INT64_TIMESTAMP int64 time; --- 670,676 ---- * Convert a interval data type to a tm structure. */ static int ! interval2tm(interval span, struct tm *tm, fsec_t *fsec) { #ifdef HAVE_INT64_TIMESTAMP int64 time; *************** *** 713,719 **** } /* interval2tm() */ static int ! tm2interval(struct tm * tm, fsec_t fsec, interval *span) { span->month = tm->tm_year * 12 + tm->tm_mon; #ifdef HAVE_INT64_TIMESTAMP --- 713,719 ---- } /* interval2tm() */ static int ! tm2interval(struct tm *tm, fsec_t fsec, interval *span) { span->month = tm->tm_year * 12 + tm->tm_mon; #ifdef HAVE_INT64_TIMESTAMP Index: src/test/regress/expected/interval.out =================================================================== RCS file: /cvsroot/pgsql/src/test/regress/expected/interval.out,v retrieving revision 1.11 diff -c -c -r1.11 interval.out *** src/test/regress/expected/interval.out 26 May 2005 02:04:14 -0000 1.11 --- src/test/regress/expected/interval.out 20 Jul 2005 03:56:29 -0000 *************** *** 28,48 **** (1 row) SELECT INTERVAL '-1 +02:03' AS "22 hours ago..."; ! 22 hours ago... ! ----------------- ! -21:57:00 (1 row) SELECT INTERVAL '-1 days +02:03' AS "22 hours ago..."; ! 22 hours ago... ! ----------------- ! -21:57:00 (1 row) SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years..."; 9 years... ---------------------------------- ! 9 years 1 mon -11 days -10:46:00 (1 row) CREATE TABLE INTERVAL_TBL (f1 interval); --- 28,48 ---- (1 row) SELECT INTERVAL '-1 +02:03' AS "22 hours ago..."; ! 22 hours ago... ! ------------------- ! -1 days +02:03:00 (1 row) SELECT INTERVAL '-1 days +02:03' AS "22 hours ago..."; ! 22 hours ago... ! ------------------- ! -1 days +02:03:00 (1 row) SELECT INTERVAL '10 years -11 month -12 days +13:14' AS "9 years..."; 9 years... ---------------------------------- ! 9 years 1 mon -12 days +13:14:00 (1 row) CREATE TABLE INTERVAL_TBL (f1 interval);