Index: src/backend/utils/adt/date.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/date.c,v retrieving revision 1.110 diff -c -r1.110 date.c *** src/backend/utils/adt/date.c 15 Jun 2005 00:34:08 -0000 1.110 --- src/backend/utils/adt/date.c 27 Jun 2005 20:30:59 -0000 *************** *** 1414,1419 **** --- 1414,1420 ---- result = (Interval *) palloc(sizeof(Interval)); result->time = time; + result->day = 0; result->month = 0; PG_RETURN_INTERVAL_P(result); Index: src/backend/utils/adt/nabstime.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v retrieving revision 1.133 diff -c -r1.133 nabstime.c *** src/backend/utils/adt/nabstime.c 15 Jun 2005 00:34:08 -0000 1.133 --- src/backend/utils/adt/nabstime.c 27 Jun 2005 20:30:59 -0000 *************** *** 916,922 **** Interval *interval = PG_GETARG_INTERVAL_P(0); RelativeTime time; int year, ! month; #ifdef HAVE_INT64_TIMESTAMP int64 span; --- 916,923 ---- Interval *interval = PG_GETARG_INTERVAL_P(0); RelativeTime time; int year, ! month, ! day; #ifdef HAVE_INT64_TIMESTAMP int64 span; *************** *** 941,952 **** 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) --- 942,963 ---- month = interval->month; } + if (interval->day == 0) + { + day = 0; + } + else + { + 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) *************** *** 964,970 **** RelativeTime reltime = PG_GETARG_RELATIVETIME(0); Interval *result; int year, ! month; result = (Interval *) palloc(sizeof(Interval)); --- 975,983 ---- RelativeTime reltime = PG_GETARG_RELATIVETIME(0); Interval *result; int year, ! month, ! day ! ; result = (Interval *) palloc(sizeof(Interval)); *************** *** 975,980 **** --- 988,994 ---- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot convert reltime \"invalid\" to interval"))); result->time = 0; + result->day = 0; result->month = 0; break; *************** *** 984,998 **** --- 998,1016 ---- reltime -= (year * (36525 * 864)); month = (reltime / (30 * SECS_PER_DAY)); reltime -= (month * (30 * SECS_PER_DAY)); + day = (reltime / (1 * 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/timestamp.c =================================================================== RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v retrieving revision 1.126 diff -c -r1.126 timestamp.c *** src/backend/utils/adt/timestamp.c 15 Jun 2005 00:34:09 -0000 1.126 --- src/backend/utils/adt/timestamp.c 27 Jun 2005 20:31:00 -0000 *************** *** 577,582 **** --- 577,583 ---- interval->time = pq_getmsgfloat8(buf); #endif interval->month = pq_getmsgint(buf, sizeof(interval->month)); + interval->day = pq_getmsgint(buf, sizeof(interval->day)); PG_RETURN_INTERVAL_P(interval); } *************** *** 597,602 **** --- 598,604 ---- pq_sendfloat8(&buf, interval->time); #endif pq_sendint(&buf, interval->month, sizeof(interval->month)); + pq_sendint(&buf, interval->day, sizeof(interval->day)); PG_RETURN_BYTEA_P(pq_endtypsend(&buf)); } *************** *** 1187,1197 **** 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); --- 1189,1206 ---- tm->tm_mon = 0; } + if (span.day != 0) + { + tm->tm_mday = span.day; + } + else + { + tm->tm_mday = 0; + } + 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); *************** *** 1199,1205 **** 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, 3600e0); TMODULO(time, tm->tm_min, 60e0); TMODULO(time, tm->tm_sec, 1e0); --- 1208,1213 ---- *************** *** 1213,1226 **** 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); --- 1221,1233 ---- 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); *************** *** 1619,1631 **** --- 1626,1643 ---- #ifdef HAVE_INT64_TIMESTAMP if (interval1->month != 0) span1 += interval1->month * INT64CONST(30) * USECS_PER_DAY; + if (interval1->day != 0) + span1 += interval1->day * INT64CONST(24) * USECS_PER_HOUR; if (interval2->month != 0) 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); + span1 += interval1->day * (24.0 * SECS_PER_HOUR); if (interval2->month != 0) 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); *************** *** 1707,1713 **** * 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. --- 1719,1726 ---- * 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. *************** *** 1897,1914 **** #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 --- 1910,1970 ---- #endif result->month = 0; + result->day = 0; + result = DatumGetIntervalP(DirectFunctionCall1(interval_simplify, + IntervalPGetDatum(result))); PG_RETURN_INTERVAL_P(result); } + /* interval_simplify() + * Consolidate the time and day fields so that: + * (a) time and day have the same sign + * (b) groups of 24 hours are set to 1 day each. + * This is useful for situations (such as non-TZ aware) where + * '1 day' = '24 hours' is valid + */ + Datum + interval_simplify(PG_FUNCTION_ARGS) + { + Interval *span = PG_GETARG_INTERVAL_P(0); + Interval *result; + + result = (Interval *) palloc(sizeof(Interval)); + result->month = 0; + result->day = 0; + result->time = 0; + + + + if (span->month != 0) + result->month = span->month; + if (span->time != 0) + result->time += span->time; + + #ifdef HAVE_INT64_TIMESTAMP + if (span->day != 0) + result->time += span->day * USECS_PER_DAY; + result->day = result->time / USECS_PER_DAY; + result->time -= result->day * USECS_PER_DAY; + + #else + if (span->day != 0) + result->time += span->day * (double) SECS_PER_DAY; + TMODULO(result->time, result->day, (double)SECS_PER_DAY); + #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 *************** *** 1956,1962 **** errmsg("timestamp out of range"))); } ! timestamp +=span->time; result = timestamp; } --- 2012,2039 ---- errmsg("timestamp out of range"))); } ! if (span->day != 0) ! { ! struct pg_tm tt, ! *tm = &tt; ! fsec_t fsec; ! ! if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) !=0) ! ereport(ERROR, ! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), ! errmsg("timestamp out of range"))); ! ! tm->tm_mday += span->day; ! ! /* need to do something here to check if it's a valid timestamp */ ! ! 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; } *************** *** 1971,1976 **** --- 2048,2054 ---- Interval tspan; tspan.month = -span->month; + tspan.day = -span->day; tspan.time = -span->time; return DirectFunctionCall2(timestamp_pl_interval, *************** *** 2037,2043 **** errmsg("timestamp out of range"))); } ! timestamp +=span->time; result = timestamp; } --- 2115,2144 ---- errmsg("timestamp out of range"))); } ! if (span->day != 0) ! { ! struct pg_tm tt, ! *tm = &tt; ! fsec_t fsec; ! ! if (timestamp2tm(timestamp, &tz, tm, &fsec, &tzn, NULL) !=0) ! ereport(ERROR, ! (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE), ! errmsg("timestamp out of range"))); ! ! tm->tm_mday += span->day; ! ! /* need to do something here to check if it's a valid timestamp */ ! ! 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; } *************** *** 2052,2057 **** --- 2153,2159 ---- Interval tspan; tspan.month = -span->month; + tspan.day = -span->day; tspan.time = -span->time; return DirectFunctionCall2(timestamptz_pl_interval, *************** *** 2069,2074 **** --- 2171,2177 ---- result = (Interval *) palloc(sizeof(Interval)); result->time = -(interval->time); + result->day = -(interval->day); result->month = -(interval->month); PG_RETURN_INTERVAL_P(result); *************** *** 2114,2119 **** --- 2217,2223 ---- 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 *************** *** 2133,2138 **** --- 2237,2243 ---- 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 *************** *** 2151,2174 **** #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 = rint(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); --- 2256,2287 ---- #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 = rint(months); + result->day = rint(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); *************** *** 2191,2198 **** --- 2304,2315 ---- float8 factor = PG_GETARG_FLOAT8(1); Interval *result; + /* should this be int64 rather than double? cf interval_mul. + * No, as we're dividing, not using integer division + */ #ifndef HAVE_INT64_TIMESTAMP double months; + double days; #endif result = (Interval *) palloc(sizeof(Interval)); *************** *** 2204,2221 **** --- 2321,2347 ---- #ifdef HAVE_INT64_TIMESTAMP result->month = (span->month / factor); + result->day = (span->day / 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; + /* evaluate fractional days as 24 hours */ + result->time += ((span->day - (result->day * factor)) * + INT64CONST(24) * USECS_PER_HOUR) / factor; #else months = span->month / factor; result->month = rint(months); + days = span->day / factor; result->time = JROUND(span->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 + result = DatumGetIntervalP(DirectFunctionCall1(interval_simplify, + IntervalPGetDatum(result))); PG_RETURN_INTERVAL_P(result); } *************** *** 2242,2248 **** /* 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"); --- 2368,2374 ---- /* We assume the input is array of interval */ deconstruct_array(transarray, ! INTERVALOID, 16, false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); *************** *** 2268,2274 **** transdatums[1] = IntervalPGetDatum(&N); result = construct_array(transdatums, 2, ! INTERVALOID, 12, false, 'd'); PG_RETURN_ARRAYTYPE_P(result); } --- 2394,2400 ---- transdatums[1] = IntervalPGetDatum(&N); result = construct_array(transdatums, 2, ! INTERVALOID, 16, false, 'd'); PG_RETURN_ARRAYTYPE_P(result); } *************** *** 2283,2290 **** 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"); --- 2409,2420 ---- N; /* We assume the input is array of interval */ + /* This has the size of interval hardcoded. + * Is there a way to use sizeof(Interval) + * to make this more robust? + */ deconstruct_array(transarray, ! INTERVALOID, 16, false, 'd', &transdatums, &ndatums); if (ndatums != 2) elog(ERROR, "expected 2-element interval array"); Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /projects/cvsroot/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.371 diff -c -r1.371 pg_proc.h *** src/include/catalog/pg_proc.h 26 Jun 2005 03:04:01 -0000 1.371 --- src/include/catalog/pg_proc.h 27 Jun 2005 20:31:01 -0000 *************** *** 1497,1502 **** --- 1497,1504 ---- 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 ( interval_simplify PGNSP PGUID 12 f f t f i 1 1186 "1186" _null_ _null_ _null_ interval_simplify - _null_ )); + DESCR("promote groups of 24 hours to numbers of days"); 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: /projects/cvsroot/pgsql/src/include/catalog/pg_type.h,v retrieving revision 1.161 diff -c -r1.161 pg_type.h *** src/include/catalog/pg_type.h 30 May 2005 01:20:50 -0000 1.161 --- src/include/catalog/pg_type.h 27 Jun 2005 20:31:02 -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: /projects/cvsroot/pgsql/src/include/utils/timestamp.h,v retrieving revision 1.45 diff -c -r1.45 timestamp.h *** src/include/utils/timestamp.h 15 Jun 2005 00:34:10 -0000 1.45 --- src/include/utils/timestamp.h 27 Jun 2005 20:31:03 -0000 *************** *** 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) *************** *** 208,213 **** --- 210,216 ---- extern Datum interval_hash(PG_FUNCTION_ARGS); extern Datum interval_smaller(PG_FUNCTION_ARGS); extern Datum interval_larger(PG_FUNCTION_ARGS); + extern Datum interval_simplify(PG_FUNCTION_ARGS); extern Datum timestamp_text(PG_FUNCTION_ARGS); extern Datum text_timestamp(PG_FUNCTION_ARGS); Index: src/test/regress/expected/interval.out =================================================================== RCS file: /projects/cvsroot/pgsql/src/test/regress/expected/interval.out,v retrieving revision 1.11 diff -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 27 Jun 2005 20:31:03 -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);