On 2020-09-04 21:45, David G. Johnston wrote:
On Thu, Sep 3, 2020 at 6:21 PM Bruce Momjian <br...@momjian.us
<mailto:br...@momjian.us>> wrote:
On Wed, Jul 15, 2020 at 09:26:53AM -0700, David G. Johnston wrote:
> Whether to actually change the behavior of to_date is up for
debate though I
> would presume it would not be back-patched.
OK, so, looking at this thread, we have to_date() treating -1 as -2 BC,
make_date() treating -1 as 1 BC, and we have Oracle, which to_date() is
supposed to match, making -1 as 1 BC.
Because we already have the to_date/make_date inconsistency, and the -1
to -2 BC mapping is confusing, and doesn't match Oracle, I think the
clean solution is to change PG 14 to treat -1 as 1 BC, and document the
incompatibility in the release notes.
I agree that someone else should write another patch to fix the behavior
for v14. Still suggest committing the proposed patch to master and all
supported versions to document the existing behavior correctly. The fix
patch can work from that.
Adding support for negative years in make_timestamp seems pretty
straightforward; see attached patch.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From ef9b0cee4aee60268d4c72e8452e74e7bbc18ed1 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Fri, 4 Sep 2020 23:02:09 +0200
Subject: [PATCH] Support negative years in make_timestamp()
make_date() already supported it; adding it was trivial.
Bug: #16419
Discussion:
https://www.postgresql.org/message-id/flat/16419-d8d9db0a7553f...@postgresql.org
---
src/backend/utils/adt/timestamp.c | 14 +++++++++-----
src/test/regress/expected/date.out | 2 ++
src/test/regress/expected/timestamp.out | 11 ++++++++++-
src/test/regress/sql/date.sql | 1 +
src/test/regress/sql/timestamp.sql | 5 ++++-
5 files changed, 26 insertions(+), 7 deletions(-)
diff --git a/src/backend/utils/adt/timestamp.c
b/src/backend/utils/adt/timestamp.c
index 5fe304cea7..4128e3a739 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -556,17 +556,21 @@ make_timestamp_internal(int year, int month, int day,
TimeOffset date;
TimeOffset time;
int dterr;
+ bool bc = false;
Timestamp result;
tm.tm_year = year;
tm.tm_mon = month;
tm.tm_mday = day;
- /*
- * Note: we'll reject zero or negative year values. Perhaps negatives
- * should be allowed to represent BC years?
- */
- dterr = ValidateDate(DTK_DATE_M, false, false, false, &tm);
+ /* Handle negative years as BC */
+ if (tm.tm_year < 0)
+ {
+ bc = true;
+ tm.tm_year = -tm.tm_year;
+ }
+
+ dterr = ValidateDate(DTK_DATE_M, false, false, bc, &tm);
if (dterr != 0)
ereport(ERROR,
diff --git a/src/test/regress/expected/date.out
b/src/test/regress/expected/date.out
index 4cdf1635f2..c03329fb5a 100644
--- a/src/test/regress/expected/date.out
+++ b/src/test/regress/expected/date.out
@@ -1439,6 +1439,8 @@ select make_time(8, 20, 0.0);
(1 row)
-- should fail
+select make_date(0, 7, 15);
+ERROR: date field value out of range: 0-07-15
select make_date(2013, 2, 30);
ERROR: date field value out of range: 2013-02-30
select make_date(2013, 13, 1);
diff --git a/src/test/regress/expected/timestamp.out
b/src/test/regress/expected/timestamp.out
index 5f97505a30..9655116090 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -1704,9 +1704,18 @@ SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5
FF6 ff1 ff2 ff3 ff4 ff
(4 rows)
-- timestamp numeric fields constructor
-SELECT make_timestamp(2014,12,28,6,30,45.887);
+SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
make_timestamp
------------------------------
Sun Dec 28 06:30:45.887 2014
(1 row)
+SELECT make_timestamp(-44, 3, 15, 12, 30, 15);
+ make_timestamp
+-----------------------------
+ Fri Mar 15 12:30:15 0044 BC
+(1 row)
+
+-- should fail
+select make_timestamp(0, 7, 15, 12, 30, 15);
+ERROR: date field value out of range: 0-07-15
diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql
index 1c3adf70ce..77f94ed27b 100644
--- a/src/test/regress/sql/date.sql
+++ b/src/test/regress/sql/date.sql
@@ -335,6 +335,7 @@ CREATE TABLE DATE_TBL (f1 date);
select make_date(-44, 3, 15);
select make_time(8, 20, 0.0);
-- should fail
+select make_date(0, 7, 15);
select make_date(2013, 2, 30);
select make_date(2013, 13, 1);
select make_date(2013, 11, -1);
diff --git a/src/test/regress/sql/timestamp.sql
b/src/test/regress/sql/timestamp.sql
index 7b58c3cfa5..727ee50084 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -240,4 +240,7 @@ CREATE TABLE TIMESTAMP_TBL (d1 timestamp(2) without time
zone);
) d(d);
-- timestamp numeric fields constructor
-SELECT make_timestamp(2014,12,28,6,30,45.887);
+SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
+SELECT make_timestamp(-44, 3, 15, 12, 30, 15);
+-- should fail
+select make_timestamp(0, 7, 15, 12, 30, 15);
--
2.28.0
