From db05f4a7eb6c80c67c25ca9d28be825b99ed3553 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 20 Jul 2005 16:42:32 +0000 Subject: [PATCH] Add 'day' field to INTERVAL so 1 day interval can be distinguished from 24 hours. This is very helpful for daylight savings time: select '2005-05-03 00:00:00 EST'::timestamp with time zone + '24 hours'; ?column? ---------------------- 2005-05-04 01:00:00-04 select '2005-05-03 00:00:00 EST'::timestamp with time zone + '1 day'; ?column? ---------------------- 2005-05-04 01:00:00-04 Michael Glaesemann --- doc/src/sgml/func.sgml | 18 +- src/backend/commands/variable.c | 12 +- src/backend/utils/adt/date.c | 6 +- src/backend/utils/adt/formatting.c | 6 +- src/backend/utils/adt/nabstime.c | 38 ++- src/backend/utils/adt/selfuncs.c | 9 +- src/backend/utils/adt/timestamp.c | 314 +++++++++++++--------- src/include/catalog/pg_proc.h | 6 +- src/include/catalog/pg_type.h | 4 +- src/include/utils/timestamp.h | 30 ++- src/interfaces/ecpg/pgtypeslib/interval.c | 10 +- src/test/regress/expected/interval.out | 14 +- 12 files changed, 286 insertions(+), 181 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9fd6ed61f8..43f720ae10 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -5144,6 +5144,22 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); true + + 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 diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c index 7df2a92a6c..dec91ef01b 100644 --- a/src/backend/commands/variable.c +++ b/src/backend/commands/variable.c @@ -9,7 +9,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/variable.c,v 1.109 2005/06/28 05:08:55 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/variable.c,v 1.110 2005/07/20 16:42:30 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -292,6 +292,15 @@ assign_timezone(const char *value, bool doit, GucSource source) 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,6 +423,7 @@ show_timezone(void) Interval interval; interval.month = 0; + interval.day = 0; #ifdef HAVE_INT64_TIMESTAMP interval.time = -(CTimeZone * USECS_PER_SEC); #else diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c index 85c2f86b24..b416db5438 100644 --- a/src/backend/utils/adt/date.c +++ b/src/backend/utils/adt/date.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.112 2005/07/12 15:17:44 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/date.c,v 1.113 2005/07/20 16:42:30 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -1423,6 +1423,7 @@ time_interval(PG_FUNCTION_ARGS) result = (Interval *) palloc(sizeof(Interval)); result->time = time; + result->day = 0; result->month = 0; PG_RETURN_INTERVAL_P(result); @@ -1477,8 +1478,9 @@ time_mi_time(PG_FUNCTION_ARGS) result = (Interval *) palloc(sizeof(Interval)); - result->time = (time1 - time2); result->month = 0; + result->day = 0; + result->time = time1 - time2; PG_RETURN_INTERVAL_P(result); } diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c index a4850f1f71..eda1967093 100644 --- a/src/backend/utils/adt/formatting.c +++ b/src/backend/utils/adt/formatting.c @@ -1,7 +1,7 @@ /* ----------------------------------------------------------------------- * formatting.c * - * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.90 2005/06/24 01:10:11 neilc Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.91 2005/07/20 16:42:30 momjian Exp $ * * * Portions Copyright (c) 1999-2005, PostgreSQL Global Development Group @@ -899,7 +899,7 @@ static char *str_tolower(char *buff); /* 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); + 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,7 +3028,7 @@ to_date(PG_FUNCTION_ARGS) */ static void do_to_timestamp(text *date_txt, text *fmt, - struct pg_tm * tm, fsec_t *fsec) + struct pg_tm *tm, fsec_t *fsec) { FormatNode *format; TmFromChar tmfc; diff --git a/src/backend/utils/adt/nabstime.c b/src/backend/utils/adt/nabstime.c index 5cef00220d..6b1cca15f8 100644 --- a/src/backend/utils/adt/nabstime.c +++ b/src/backend/utils/adt/nabstime.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/nabstime.c,v 1.135 2005/07/12 16:04:56 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/nabstime.c,v 1.136 2005/07/20 16:42:30 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -829,7 +829,8 @@ interval_reltime(PG_FUNCTION_ARGS) Interval *interval = PG_GETARG_INTERVAL_P(0); RelativeTime time; int year, - month; + month, + day; #ifdef HAVE_INT64_TIMESTAMP int64 span; @@ -837,28 +838,17 @@ interval_reltime(PG_FUNCTION_ARGS) 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; - } + year = interval->month / 12; + month = interval->month % 12; + day = interval->day; #ifdef HAVE_INT64_TIMESTAMP - span = ((INT64CONST(365250000) * year + INT64CONST(30000000) * month) * - INT64CONST(86400)) + interval->time; + 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) * SECS_PER_DAY + interval->time; + span = (365.25 * year + 30.0 * month + day) * SECS_PER_DAY + interval->time; #endif if (span < INT_MIN || span > INT_MAX) @@ -876,7 +866,8 @@ reltime_interval(PG_FUNCTION_ARGS) RelativeTime reltime = PG_GETARG_RELATIVETIME(0); Interval *result; int year, - month; + month, + day; result = (Interval *) palloc(sizeof(Interval)); @@ -887,6 +878,7 @@ reltime_interval(PG_FUNCTION_ARGS) (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot convert reltime \"invalid\" to interval"))); result->time = 0; + result->day = 0; result->month = 0; break; @@ -896,15 +888,19 @@ reltime_interval(PG_FUNCTION_ARGS) 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; } diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 85dd4196c6..33726945a3 100644 --- a/src/backend/utils/adt/selfuncs.c +++ b/src/backend/utils/adt/selfuncs.c @@ -15,7 +15,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.184 2005/07/12 16:04:57 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/selfuncs.c,v 1.185 2005/07/20 16:42:30 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -2784,10 +2784,11 @@ convert_timevalue_to_scalar(Datum value, Oid typid) * too accurate, but plenty good enough for our purposes. */ #ifdef HAVE_INT64_TIMESTAMP - return (interval->time + (interval->month * ((365.25 / 12.0) * 86400000000.0))); + return interval->time + interval->day * (double)USECS_PER_DAY + + interval->month * ((365.25 / 12.0) * USECS_PER_DAY); #else - return interval->time + - interval ->month * (365.25 / 12.0 * 24.0 * 60.0 * 60.0); + return interval->time + interval->day * SECS_PER_DAY + + interval->month * ((365.25 / 12.0) * (double)SECS_PER_DAY); #endif } case RELTIMEOID: diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c index be86884205..dd9e72daa3 100644 --- a/src/backend/utils/adt/timestamp.c +++ b/src/backend/utils/adt/timestamp.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.133 2005/07/20 03:50:24 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.134 2005/07/20 16:42:31 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -596,6 +596,7 @@ interval_recv(PG_FUNCTION_ARGS) #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,6 +619,7 @@ interval_send(PG_FUNCTION_ARGS) #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,48 +699,37 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) 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; - -#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 + interval->time = 0; } else if (range == INTERVAL_MASK(HOUR)) { -#ifdef HAVE_INT64_TIMESTAMP - int64 day; -#else - double day; -#endif - interval->month = 0; + interval->day = 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; + interval->time = ((int)(interval->time / 3600)) * 3600.0; #endif } else if (range == INTERVAL_MASK(MINUTE)) @@ -750,6 +741,7 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) #endif interval->month = 0; + interval->day = 0; #ifdef HAVE_INT64_TIMESTAMP hour = interval->time / USECS_PER_HOUR; @@ -759,27 +751,26 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) #else TMODULO(interval->time, hour, 3600.0); - interval->time = ((int) (interval->time / 60)) * 60; + 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); -/* interval->time = (int)(interval->time); */ + /* return subseconds too */ #endif } /* DAY TO HOUR */ @@ -791,9 +782,8 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_HOUR) * USECS_PER_HOUR; - #else - interval->time = ((int) (interval->time / 3600)) * 3600; + interval->time = ((int) (interval->time / 3600)) * 3600.0; #endif } /* DAY TO MINUTE */ @@ -806,9 +796,8 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) #ifdef HAVE_INT64_TIMESTAMP interval->time = (interval->time / USECS_PER_MINUTE) * USECS_PER_MINUTE; - #else - interval->time = ((int) (interval->time / 60)) * 60; + interval->time = ((int)(interval->time / 60)) * 60.0; #endif } /* DAY TO SECOND */ @@ -822,24 +811,14 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) else if (range == (INTERVAL_MASK(HOUR) | INTERVAL_MASK(MINUTE))) { -#ifdef HAVE_INT64_TIMESTAMP - int64 day; - -#else - double day; -#endif - interval->month = 0; + interval->day = 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; + interval->time = ((int)(interval->time / 60)) * 60.0; #endif } /* HOUR TO SECOND */ @@ -847,22 +826,9 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) 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 + interval->day = 0; + /* return subseconds too */ } /* MINUTE TO SECOND */ else if (range == (INTERVAL_MASK(MINUTE) | @@ -876,6 +842,7 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod) #endif interval->month = 0; + interval->day = 0; #ifdef HAVE_INT64_TIMESTAMP hour = interval->time / USECS_PER_HOUR; @@ -1029,7 +996,7 @@ dt2time(Timestamp jd, int *hour, int *min, int *sec, fsec_t *fsec) * timezone) will be used. */ int -timestamp2tm(Timestamp dt, int *tzp, struct pg_tm * tm, fsec_t *fsec, char **tzn, pg_tz *attimezone) +timestamp2tm(Timestamp dt, int *tzp, struct pg_tm *tm, fsec_t *fsec, char **tzn, pg_tz *attimezone) { Timestamp date; Timestamp time; @@ -1165,7 +1132,7 @@ timestamp2tm(Timestamp dt, int *tzp, struct pg_tm * tm, fsec_t *fsec, char **tzn * Returns -1 on failure (value out of range). */ int -tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result) +tm2timestamp(struct pg_tm *tm, fsec_t fsec, int *tzp, Timestamp *result) { #ifdef HAVE_INT64_TIMESTAMP int date; @@ -1205,7 +1172,7 @@ tm2timestamp(struct pg_tm * tm, fsec_t fsec, int *tzp, Timestamp *result) * Convert a interval data type to a tm structure. */ int -interval2tm(Interval span, struct pg_tm * tm, fsec_t *fsec) +interval2tm(Interval span, struct pg_tm *tm, fsec_t *fsec) { #ifdef HAVE_INT64_TIMESTAMP int64 time; @@ -1213,23 +1180,12 @@ interval2tm(Interval span, struct pg_tm * tm, fsec_t *fsec) 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; - } - + 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_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); @@ -1237,7 +1193,6 @@ interval2tm(Interval span, struct pg_tm * tm, fsec_t *fsec) 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); @@ -1248,17 +1203,16 @@ interval2tm(Interval span, struct pg_tm * tm, fsec_t *fsec) } int -tm2interval(struct pg_tm * tm, fsec_t fsec, Interval *span) +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_mday * INT64CONST(24)) + - tm->tm_hour) * INT64CONST(60)) + + span->time = (((((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) + + span->time = (((tm->tm_hour * 60.0) + tm->tm_min) * 60.0) + tm->tm_sec; span->time = JROUND(span->time + fsec); @@ -1320,7 +1274,7 @@ interval_finite(PG_FUNCTION_ARGS) *---------------------------------------------------------*/ void -GetEpochTime(struct pg_tm * tm) +GetEpochTime(struct pg_tm *tm) { struct pg_tm *t0; pg_time_t epoch = 0; @@ -1654,15 +1608,15 @@ interval_cmp_internal(Interval *interval1, Interval *interval2) 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; + 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 - if (interval1->month != 0) - span1 += interval1->month * (30.0 * SECS_PER_DAY); - if (interval2->month != 0) - span2 += interval2->month * (30.0 * SECS_PER_DAY); + 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,7 +1698,8 @@ interval_hash(PG_FUNCTION_ARGS) * 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)); + return hash_any((unsigned char *) key, + sizeof(key->time) + sizeof(key->day) + sizeof(key->month)); } /* overlaps_timestamp() --- implements the SQL92 OVERLAPS operator. @@ -1934,18 +1889,76 @@ timestamp_mi(PG_FUNCTION_ARGS) #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 + * 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,7 +1970,6 @@ timestamp_pl_interval(PG_FUNCTION_ARGS) if (TIMESTAMP_NOT_FINITE(timestamp)) result = timestamp; - else { if (span->month != 0) @@ -1993,7 +2005,29 @@ timestamp_pl_interval(PG_FUNCTION_ARGS) errmsg("timestamp out of range"))); } - timestamp +=span->time; + 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,6 +2042,7 @@ timestamp_mi_interval(PG_FUNCTION_ARGS) Interval tspan; tspan.month = -span->month; + tspan.day = -span->day; tspan.time = -span->time; return DirectFunctionCall2(timestamp_pl_interval, @@ -2036,7 +2071,6 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS) if (TIMESTAMP_NOT_FINITE(timestamp)) result = timestamp; - else { if (span->month != 0) @@ -2074,7 +2108,31 @@ timestamptz_pl_interval(PG_FUNCTION_ARGS) errmsg("timestamp out of range"))); } - timestamp +=span->time; + 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,6 +2147,7 @@ timestamptz_mi_interval(PG_FUNCTION_ARGS) Interval tspan; tspan.month = -span->month; + tspan.day = -span->day; tspan.time = -span->time; return DirectFunctionCall2(timestamptz_pl_interval, @@ -2106,6 +2165,7 @@ interval_um(PG_FUNCTION_ARGS) result = (Interval *) palloc(sizeof(Interval)); result->time = -(interval->time); + result->day = -(interval->day); result->month = -(interval->month); PG_RETURN_INTERVAL_P(result); @@ -2151,6 +2211,7 @@ interval_pl(PG_FUNCTION_ARGS) 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,6 +2231,7 @@ interval_mi(PG_FUNCTION_ARGS) 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,23 +2250,30 @@ interval_mul(PG_FUNCTION_ARGS) #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 += (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,12 +2294,9 @@ interval_div(PG_FUNCTION_ARGS) { Interval *span = PG_GETARG_INTERVAL_P(0); float8 factor = PG_GETARG_FLOAT8(1); + double month_remainder, day_remainder; Interval *result; -#ifndef HAVE_INT64_TIMESTAMP - double months; -#endif - result = (Interval *) palloc(sizeof(Interval)); if (factor == 0.0) @@ -2238,20 +2304,29 @@ interval_div(PG_FUNCTION_ARGS) (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->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; + result->time += day_remainder * USECS_PER_DAY; #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); + 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,9 +2351,8 @@ interval_accum(PG_FUNCTION_ARGS) Interval *newsum; ArrayType *result; - /* We assume the input is array of interval */ deconstruct_array(transarray, - INTERVALOID, 12, false, 'd', + INTERVALOID, sizeof(Interval), false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); @@ -2304,7 +2378,7 @@ interval_accum(PG_FUNCTION_ARGS) transdatums[1] = IntervalPGetDatum(&N); result = construct_array(transdatums, 2, - INTERVALOID, 12, false, 'd'); + INTERVALOID, sizeof(Interval), false, 'd'); PG_RETURN_ARRAYTYPE_P(result); } @@ -2318,9 +2392,8 @@ interval_avg(PG_FUNCTION_ARGS) Interval sumX, N; - /* We assume the input is array of interval */ deconstruct_array(transarray, - INTERVALOID, 12, false, 'd', + INTERVALOID, sizeof(Interval), false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); @@ -2721,7 +2794,7 @@ interval_text(PG_FUNCTION_ARGS) result = palloc(len); VARATT_SIZEP(result) = len; - memmove(VARDATA(result), str, (len - VARHDRSZ)); + memmove(VARDATA(result), str, len - VARHDRSZ); pfree(str); @@ -3080,6 +3153,7 @@ interval_trunc(PG_FUNCTION_ARGS) { switch (val) { + /* fall through */ case DTK_MILLENNIUM: /* caution: C division may have negative remainder */ tm->tm_year = (tm->tm_year / 1000) * 1000; @@ -3830,11 +3904,9 @@ interval_part(PG_FUNCTION_ARGS) #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); - } + 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 { diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index f0ecaa9d9b..ca04d11c30 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.376 2005/07/10 21:13:59 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.377 2005/07/20 16:42:31 momjian Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -1497,6 +1497,10 @@ DATA(insert OID = 1173 ( timestamptz PGNSP PGUID 12 f f t f i 1 1184 "702" _ 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_ )); diff --git a/src/include/catalog/pg_type.h b/src/include/catalog/pg_type.h index 9feeccedfd..5712e0d692 100644 --- a/src/include/catalog/pg_type.h +++ b/src/include/catalog/pg_type.h @@ -8,7 +8,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_type.h,v 1.163 2005/07/07 20:39:59 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_type.h,v 1.164 2005/07/20 16:42:32 momjian Exp $ * * NOTES * the genbki.sh script reads this file and generates .bki @@ -457,7 +457,7 @@ DATA(insert OID = 1184 ( timestamptz PGNSP PGUID 8 f b t \054 0 0 timestamptz_in 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_ )); +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_ )); diff --git a/src/include/utils/timestamp.h b/src/include/utils/timestamp.h index 1cd591326c..5cf8fd25fe 100644 --- a/src/include/utils/timestamp.h +++ b/src/include/utils/timestamp.h @@ -6,7 +6,7 @@ * Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.46 2005/06/29 22:51:57 tgl Exp $ + * $PostgreSQL: pgsql/src/include/utils/timestamp.h,v 1.47 2005/07/20 16:42:32 momjian Exp $ * *------------------------------------------------------------------------- */ @@ -25,9 +25,9 @@ /* * 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. + * 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,12 +45,13 @@ typedef double TimestampTz; typedef struct { #ifdef HAVE_INT64_TIMESTAMP - int64 time; /* all time units other than months and - * years */ + int64 time; /* all time units other than days, + * months and years */ #else - double time; /* all time units other than months and - * years */ + 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,6 +61,7 @@ typedef struct #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,6 +214,8 @@ extern Datum interval_cmp(PG_FUNCTION_ARGS); 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,16 +270,16 @@ extern Datum pgsql_postmaster_start_time(PG_FUNCTION_ARGS); 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, +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 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 void GetEpochTime(struct pg_tm *tm); extern int timestamp_cmp_internal(Timestamp dt1, Timestamp dt2); diff --git a/src/interfaces/ecpg/pgtypeslib/interval.c b/src/interfaces/ecpg/pgtypeslib/interval.c index 4871bdeeab..0f0eb8be16 100644 --- a/src/interfaces/ecpg/pgtypeslib/interval.c +++ b/src/interfaces/ecpg/pgtypeslib/interval.c @@ -33,7 +33,7 @@ TrimTrailingZeros(char *str) * can be used to represent time spans. */ static int -DecodeTime(char *str, int fmask, int *tmask, struct tm * tm, fsec_t *fsec) +DecodeTime(char *str, int fmask, int *tmask, struct tm *tm, fsec_t *fsec) { char *cp; @@ -107,7 +107,7 @@ DecodeTime(char *str, int fmask, int *tmask, struct tm * tm, fsec_t *fsec) * 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) +DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm *tm, fsec_t *fsec) { int is_before = FALSE; @@ -445,7 +445,7 @@ DecodeInterval(char **field, int *ftype, int nf, int *dtype, struct tm * tm, fse * - thomas 1998-04-30 */ int -EncodeInterval(struct tm * tm, fsec_t fsec, int style, char *str) +EncodeInterval(struct tm *tm, fsec_t fsec, int style, char *str) { int is_before = FALSE; int is_nonzero = FALSE; @@ -670,7 +670,7 @@ EncodeInterval(struct tm * tm, fsec_t fsec, int style, char *str) * Convert a interval data type to a tm structure. */ static int -interval2tm(interval span, struct tm * tm, fsec_t *fsec) +interval2tm(interval span, struct tm *tm, fsec_t *fsec) { #ifdef HAVE_INT64_TIMESTAMP int64 time; @@ -713,7 +713,7 @@ interval2tm(interval span, struct tm * tm, fsec_t *fsec) } /* interval2tm() */ static int -tm2interval(struct tm * tm, fsec_t fsec, interval *span) +tm2interval(struct tm *tm, fsec_t fsec, interval *span) { span->month = tm->tm_year * 12 + tm->tm_mon; #ifdef HAVE_INT64_TIMESTAMP diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out index daa6348785..7480e7f2e5 100644 --- a/src/test/regress/expected/interval.out +++ b/src/test/regress/expected/interval.out @@ -28,21 +28,21 @@ SELECT INTERVAL '-05' AS "Five hours"; (1 row) SELECT INTERVAL '-1 +02:03' AS "22 hours ago..."; - 22 hours ago... ------------------ - -21:57:00 + 22 hours ago... +------------------- + -1 days +02:03:00 (1 row) SELECT INTERVAL '-1 days +02:03' AS "22 hours ago..."; - 22 hours ago... ------------------ - -21:57:00 + 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 -11 days -10:46:00 + 9 years 1 mon -12 days +13:14:00 (1 row) CREATE TABLE INTERVAL_TBL (f1 interval); -- 2.40.0