aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--src/backend/utils/adt/timestamp.c80
-rw-r--r--src/test/regress/expected/interval.out108
-rw-r--r--src/test/regress/sql/interval.sql9
3 files changed, 114 insertions, 83 deletions
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 507f74a71cd..3fb4ebe2857 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.199 2009/05/26 02:17:50 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/utils/adt/timestamp.c,v 1.200 2009/06/01 23:55:15 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -955,13 +955,32 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
/*
* Unspecified range and precision? Then not necessary to adjust. Setting
- * typmod to -1 is the convention for all types.
+ * typmod to -1 is the convention for all data types.
*/
if (typmod >= 0)
{
int range = INTERVAL_RANGE(typmod);
int precision = INTERVAL_PRECISION(typmod);
+ /*
+ * Our interpretation of intervals with a limited set of fields
+ * is that fields to the right of the last one specified are zeroed
+ * out, but those to the left of it remain valid. Thus for example
+ * there is no operational difference between INTERVAL YEAR TO MONTH
+ * and INTERVAL MONTH. In some cases we could meaningfully enforce
+ * that higher-order fields are zero; for example INTERVAL DAY could
+ * reject nonzero "month" field. However that seems a bit pointless
+ * when we can't do it consistently. (We cannot enforce a range limit
+ * on the highest expected field, since we do not have any equivalent
+ * of SQL's <interval leading field precision>.)
+ *
+ * Note: before PG 8.4 we interpreted a limited set of fields as
+ * actually causing a "modulo" operation on a given value, potentially
+ * losing high-order as well as low-order information. But there is
+ * no support for such behavior in the standard, and it seems fairly
+ * undesirable on data consistency grounds anyway. Now we only
+ * perform truncation or rounding of low-order fields.
+ */
if (range == INTERVAL_FULL_RANGE)
{
/* Do nothing... */
@@ -974,27 +993,21 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
}
else if (range == INTERVAL_MASK(MONTH))
{
- interval->month %= MONTHS_PER_YEAR;
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;
@@ -1004,42 +1017,21 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
}
else if (range == INTERVAL_MASK(MINUTE))
{
- TimeOffset hour;
-
- interval->month = 0;
- interval->day = 0;
-
#ifdef HAVE_INT64_TIMESTAMP
- hour = interval->time / USECS_PER_HOUR;
- interval->time -= hour * USECS_PER_HOUR;
interval->time = (interval->time / USECS_PER_MINUTE) *
USECS_PER_MINUTE;
#else
- TMODULO(interval->time, hour, (double) SECS_PER_HOUR);
interval->time = ((int) (interval->time / SECS_PER_MINUTE)) * (double) SECS_PER_MINUTE;
#endif
}
else if (range == INTERVAL_MASK(SECOND))
{
- TimeOffset minute;
-
- 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, (double) SECS_PER_MINUTE);
- /* return subseconds too */
-#endif
+ /* fractional-second rounding will be dealt with below */
}
/* DAY TO HOUR */
else if (range == (INTERVAL_MASK(DAY) |
INTERVAL_MASK(HOUR)))
{
- interval->month = 0;
-
#ifdef HAVE_INT64_TIMESTAMP
interval->time = (interval->time / USECS_PER_HOUR) *
USECS_PER_HOUR;
@@ -1052,8 +1044,6 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
INTERVAL_MASK(HOUR) |
INTERVAL_MASK(MINUTE)))
{
- interval->month = 0;
-
#ifdef HAVE_INT64_TIMESTAMP
interval->time = (interval->time / USECS_PER_MINUTE) *
USECS_PER_MINUTE;
@@ -1066,15 +1056,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
INTERVAL_MASK(HOUR) |
INTERVAL_MASK(MINUTE) |
INTERVAL_MASK(SECOND)))
- interval->month = 0;
-
+ {
+ /* fractional-second rounding will be dealt with below */
+ }
/* HOUR TO MINUTE */
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;
@@ -1087,25 +1075,13 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
INTERVAL_MASK(MINUTE) |
INTERVAL_MASK(SECOND)))
{
- interval->month = 0;
- interval->day = 0;
- /* return subseconds too */
+ /* fractional-second rounding will be dealt with below */
}
/* MINUTE TO SECOND */
else if (range == (INTERVAL_MASK(MINUTE) |
INTERVAL_MASK(SECOND)))
{
- TimeOffset hour;
-
- interval->month = 0;
- interval->day = 0;
-
-#ifdef HAVE_INT64_TIMESTAMP
- hour = interval->time / USECS_PER_HOUR;
- interval->time -= hour * USECS_PER_HOUR;
-#else
- TMODULO(interval->time, hour, (double) SECS_PER_HOUR);
-#endif
+ /* fractional-second rounding will be dealt with below */
}
else
elog(ERROR, "unrecognized interval typmod: %d", typmod);
@@ -1148,8 +1124,6 @@ AdjustIntervalForTypmod(Interval *interval, int32 typmod)
#endif
}
}
-
- return;
}
diff --git a/src/test/regress/expected/interval.out b/src/test/regress/expected/interval.out
index 9effe61f42b..cf378867c73 100644
--- a/src/test/regress/expected/interval.out
+++ b/src/test/regress/expected/interval.out
@@ -358,12 +358,46 @@ SELECT '1:20:05 5 microseconds'::interval; -- error
ERROR: invalid input syntax for type interval: "1:20:05 5 microseconds"
LINE 1: SELECT '1:20:05 5 microseconds'::interval;
^
+SELECT '1 day 1 day'::interval; -- error
+ERROR: invalid input syntax for type interval: "1 day 1 day"
+LINE 1: SELECT '1 day 1 day'::interval;
+ ^
SELECT interval '1-2'; -- SQL year-month literal
interval
---------------
1 year 2 mons
(1 row)
+SELECT interval '999' second; -- oversize leading field is ok
+ interval
+----------
+ 00:16:39
+(1 row)
+
+SELECT interval '999' minute;
+ interval
+----------
+ 16:39:00
+(1 row)
+
+SELECT interval '999' hour;
+ interval
+-----------
+ 999:00:00
+(1 row)
+
+SELECT interval '999' day;
+ interval
+----------
+ 999 days
+(1 row)
+
+SELECT interval '999' month;
+ interval
+-----------------
+ 83 years 3 mons
+(1 row)
+
-- test SQL-spec syntaxes for restricted field sets
SELECT interval '1' year;
interval
@@ -472,15 +506,15 @@ ERROR: invalid input syntax for type interval: "1 2"
LINE 1: SELECT interval '1 2' hour to minute;
^
SELECT interval '1 2:03' hour to minute;
- interval
-----------
- 02:03:00
+ interval
+----------------
+ 1 day 02:03:00
(1 row)
SELECT interval '1 2:03:04' hour to minute;
- interval
-----------
- 02:03:00
+ interval
+----------------
+ 1 day 02:03:00
(1 row)
SELECT interval '1 2' hour to second;
@@ -488,15 +522,15 @@ ERROR: invalid input syntax for type interval: "1 2"
LINE 1: SELECT interval '1 2' hour to second;
^
SELECT interval '1 2:03' hour to second;
- interval
-----------
- 02:03:00
+ interval
+----------------
+ 1 day 02:03:00
(1 row)
SELECT interval '1 2:03:04' hour to second;
- interval
-----------
- 02:03:04
+ interval
+----------------
+ 1 day 02:03:04
(1 row)
SELECT interval '1 2' minute to second;
@@ -504,17 +538,31 @@ ERROR: invalid input syntax for type interval: "1 2"
LINE 1: SELECT interval '1 2' minute to second;
^
SELECT interval '1 2:03' minute to second;
- interval
-----------
- 00:02:03
+ interval
+----------------
+ 1 day 00:02:03
(1 row)
SELECT interval '1 2:03:04' minute to second;
- interval
-----------
- 00:03:04
+ interval
+----------------
+ 1 day 02:03:04
(1 row)
+SELECT interval '123 11' day to hour; -- ok
+ interval
+-------------------
+ 123 days 11:00:00
+(1 row)
+
+SELECT interval '123 11' day; -- not ok
+ERROR: invalid input syntax for type interval: "123 11"
+LINE 1: SELECT interval '123 11' day;
+ ^
+SELECT interval '123 11'; -- not ok, too ambiguous
+ERROR: invalid input syntax for type interval: "123 11"
+LINE 1: SELECT interval '123 11';
+ ^
-- test syntaxes for restricted precision
SELECT interval(0) '1 day 01:23:45.6789';
interval
@@ -585,15 +633,15 @@ ERROR: invalid input syntax for type interval: "1 2.345"
LINE 1: SELECT interval '1 2.345' hour to second(2);
^
SELECT interval '1 2:03.45678' hour to second(2);
- interval
--------------
- 00:02:03.46
+ interval
+-------------------
+ 1 day 00:02:03.46
(1 row)
SELECT interval '1 2:03:04.5678' hour to second(2);
- interval
--------------
- 02:03:04.57
+ interval
+-------------------
+ 1 day 02:03:04.57
(1 row)
SELECT interval '1 2.3456' minute to second(2);
@@ -601,15 +649,15 @@ ERROR: invalid input syntax for type interval: "1 2.3456"
LINE 1: SELECT interval '1 2.3456' minute to second(2);
^
SELECT interval '1 2:03.5678' minute to second(2);
- interval
--------------
- 00:02:03.57
+ interval
+-------------------
+ 1 day 00:02:03.57
(1 row)
SELECT interval '1 2:03:04.5678' minute to second(2);
- interval
--------------
- 00:03:04.57
+ interval
+-------------------
+ 1 day 02:03:04.57
(1 row)
-- test inputting and outputting SQL standard interval literals
diff --git a/src/test/regress/sql/interval.sql b/src/test/regress/sql/interval.sql
index 2a903a940dc..f342a18af2d 100644
--- a/src/test/regress/sql/interval.sql
+++ b/src/test/regress/sql/interval.sql
@@ -130,7 +130,13 @@ SELECT '1 second 2 seconds'::interval; -- error
SELECT '10 milliseconds 20 milliseconds'::interval; -- error
SELECT '5.5 seconds 3 milliseconds'::interval; -- error
SELECT '1:20:05 5 microseconds'::interval; -- error
+SELECT '1 day 1 day'::interval; -- error
SELECT interval '1-2'; -- SQL year-month literal
+SELECT interval '999' second; -- oversize leading field is ok
+SELECT interval '999' minute;
+SELECT interval '999' hour;
+SELECT interval '999' day;
+SELECT interval '999' month;
-- test SQL-spec syntaxes for restricted field sets
SELECT interval '1' year;
@@ -159,6 +165,9 @@ SELECT interval '1 2:03:04' hour to second;
SELECT interval '1 2' minute to second;
SELECT interval '1 2:03' minute to second;
SELECT interval '1 2:03:04' minute to second;
+SELECT interval '123 11' day to hour; -- ok
+SELECT interval '123 11' day; -- not ok
+SELECT interval '123 11'; -- not ok, too ambiguous
-- test syntaxes for restricted precision
SELECT interval(0) '1 day 01:23:45.6789';