Fix EXTRACT(ISOYEAR FROM timestamp) for years BC.

The test cases added by commit 26ae3aa80 exposed an old oversight in
timestamp[tz]_part: they didn't correct the result of date2isoyear()
for BC years, so that we produced an off-by-one answer for such years.
Fix that, and back-patch to all supported branches.

Discussion: https://postgr.es/m/SG2PR06MB37762CAE45DB0F6CA7001EA9B6550@SG2PR06MB3776.apcprd06.prod.outlook.com

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index dba0306c1ab0db9b6b8d9768c0f93bc42cdfcd8b..c87c95fb648721a6f8ae4ed10b3ec102bac7a813 100644 (file)
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4348,6 +4348,7 @@ date2isoweek(int year, int mon, int mday)
 /* date2isoyear()
  *
  * Returns ISO 8601 year number.
+ * Note: zero or negative results follow the year-zero-exists convention.
  */
 int
 date2isoyear(int year, int mon, int mday)
@@ -4556,6 +4557,9 @@ timestamp_part(PG_FUNCTION_ARGS)
 
            case DTK_ISOYEAR:
                result = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
+               /* Adjust BC years */
+               if (result <= 0)
+                   result -= 1;
                break;
 
            case DTK_DOW:
@@ -4762,6 +4766,9 @@ timestamptz_part(PG_FUNCTION_ARGS)
 
            case DTK_ISOYEAR:
                result = date2isoyear(tm->tm_year, tm->tm_mon, tm->tm_mday);
+               /* Adjust BC years */
+               if (result <= 0)
+                   result -= 1;
                break;
 
            case DTK_DOW:

diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 6c6eb9cf6e0e192bbfd2d377809024e9635de120..5a749f3a5bffaaa4bb0d96ae96fb718ae981b963 100644 (file)
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -810,7 +810,7 @@ SELECT d1 as "timestamp",
  Fri Feb 14 17:32:01 1997    |    1997 |    7 |      5 |   5 |  45
  Sat Feb 15 17:32:01 1997    |    1997 |    7 |      6 |   6 |  46
  Sun Feb 16 17:32:01 1997    |    1997 |    7 |      7 |   0 |  47
- Tue Feb 16 17:32:01 0097 BC |     -96 |    7 |      2 |   2 |  47
+ Tue Feb 16 17:32:01 0097 BC |     -97 |    7 |      2 |   2 |  47
  Sat Feb 16 17:32:01 0097    |      97 |    7 |      6 |   6 |  47
  Thu Feb 16 17:32:01 0597    |     597 |    7 |      4 |   4 |  47
  Tue Feb 16 17:32:01 1097    |    1097 |    7 |      2 |   2 |  47

diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 752af9f8ca959e7adb0da8d9cf120a8fa8dc8545..feeeacfdcba2f4b6340b237a7088d82fe6c8fcd2 100644 (file)
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -889,7 +889,7 @@ SELECT d1 as timestamptz,
  Fri Feb 14 17:32:01 1997 PST    |    1997 |    7 |      5 |   5 |  45
  Sat Feb 15 17:32:01 1997 PST    |    1997 |    7 |      6 |   6 |  46
  Sun Feb 16 17:32:01 1997 PST    |    1997 |    7 |      7 |   0 |  47
- Tue Feb 16 17:32:01 0097 PST BC |     -96 |    7 |      2 |   2 |  47
+ Tue Feb 16 17:32:01 0097 PST BC |     -97 |    7 |      2 |   2 |  47
  Sat Feb 16 17:32:01 0097 PST    |      97 |    7 |      6 |   6 |  47
  Thu Feb 16 17:32:01 0597 PST    |     597 |    7 |      4 |   4 |  47
  Tue Feb 16 17:32:01 1097 PST    |    1097 |    7 |      2 |   2 |  47