postgres.git / summary / log / commit / refs
commit 6e2ac8d66d58ccdda4dcccc52f8b6b7911db0ad2
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu Dec 12 17:30:44 2019 +0000
First released in: 13.0, 9.4.26, 9.5.21, 9.6.17, 10.12, 11.7, 12.2
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
src/backend/utils/adt/timestamp.c | 7 +++++++
src/test/regress/expected/timestamp.out | 2 +-
src/test/regress/expected/timestamptz.out | 2 +-
3 files changed, 9 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index 27211efc052..e83065ec757 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -4492,6 +4492,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)
@@ -4783,6 +4784,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:
@@ -5000,6 +5004,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 ce5e06c9185..3ff048983d1 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -822,7 +822,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 d428fa9b7ca..5f6c6af1545 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -913,7 +913,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
[parent: 6bed9b4f6ea5]