Skip to content

Commit a094c8f

Browse files
tglsfdctglsfdc
committed
Fix make_timestamp[tz] to accept negative years as meaning BC.
Previously we threw an error. But make_date already allowed the case, so it is inconsistent as well as unhelpful for make_timestamp not to. Both functions continue to reject year zero. Code and test fixes by Peter Eisentraut, doc changes by me Discussion: https://postgr.es/m/13c0992c-f15a-a0ca-d839-91d3efd965d9@2ndquadrant.com
1 parent a6b1f53 commit a094c8f

File tree

6 files changed

+36
-9
lines changed

6 files changed

+36
-9
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8939,6 +8939,7 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
89398939
</para>
89408940
<para>
89418941
Create date from year, month and day fields
8942+
(negative years signify BC)
89428943
</para>
89438944
<para>
89448945
<literal>make_date(2013, 7, 15)</literal>
@@ -9004,6 +9005,7 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
90049005
</para>
90059006
<para>
90069007
Create timestamp from year, month, day, hour, minute and seconds fields
9008+
(negative years signify BC)
90079009
</para>
90089010
<para>
90099011
<literal>make_timestamp(2013, 7, 15, 8, 15, 23.5)</literal>
@@ -9027,12 +9029,18 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
90279029
</para>
90289030
<para>
90299031
Create timestamp with time zone from year, month, day, hour, minute
9030-
and seconds fields; if <parameter>timezone</parameter> is not
9031-
specified, the current time zone is used
9032+
and seconds fields (negative years signify BC).
9033+
If <parameter>timezone</parameter> is not
9034+
specified, the current time zone is used; the examples assume the
9035+
session time zone is <literal>Europe/London</literal>
90329036
</para>
90339037
<para>
90349038
<literal>make_timestamptz(2013, 7, 15, 8, 15, 23.5)</literal>
90359039
<returnvalue>2013-07-15 08:15:23.5+01</returnvalue>
9040+
</para>
9041+
<para>
9042+
<literal>make_timestamptz(2013, 7, 15, 8, 15, 23.5, 'America/New_York')</literal>
9043+
<returnvalue>2013-07-15 13:15:23.5+01</returnvalue>
90369044
</para></entry>
90379045
</row>
90389046

‎src/backend/utils/adt/timestamp.c

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -556,17 +556,21 @@ make_timestamp_internal(int year, int month, int day,
556556
TimeOffset date;
557557
TimeOffset time;
558558
int dterr;
559+
bool bc = false;
559560
Timestamp result;
560561

561562
tm.tm_year = year;
562563
tm.tm_mon = month;
563564
tm.tm_mday = day;
564565

565-
/*
566-
* Note: we'll reject zero or negative year values. Perhaps negatives
567-
* should be allowed to represent BC years?
568-
*/
569-
dterr = ValidateDate(DTK_DATE_M, false, false, false, &tm);
566+
/* Handle negative years as BC */
567+
if (tm.tm_year < 0)
568+
{
569+
bc = true;
570+
tm.tm_year = -tm.tm_year;
571+
}
572+
573+
dterr = ValidateDate(DTK_DATE_M, false, false, bc, &tm);
570574

571575
if (dterr != 0)
572576
ereport(ERROR,

‎src/test/regress/expected/date.out

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1607,6 +1607,8 @@ select make_time(8, 20, 0.0);
16071607
(1 row)
16081608

16091609
-- should fail
1610+
select make_date(0, 7, 15);
1611+
ERROR: date field value out of range: 0-07-15
16101612
select make_date(2013, 2, 30);
16111613
ERROR: date field value out of range: 2013-02-30
16121614
select make_date(2013, 13, 1);

‎src/test/regress/expected/timestamp.out

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1704,9 +1704,18 @@ SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff
17041704
(4 rows)
17051705

17061706
-- timestamp numeric fields constructor
1707-
SELECT make_timestamp(2014,12,28,6,30,45.887);
1707+
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
17081708
make_timestamp
17091709
------------------------------
17101710
Sun Dec 28 06:30:45.887 2014
17111711
(1 row)
17121712

1713+
SELECT make_timestamp(-44, 3, 15, 12, 30, 15);
1714+
make_timestamp
1715+
-----------------------------
1716+
Fri Mar 15 12:30:15 0044 BC
1717+
(1 row)
1718+
1719+
-- should fail
1720+
select make_timestamp(0, 7, 15, 12, 30, 15);
1721+
ERROR: date field value out of range: 0-07-15

‎src/test/regress/sql/date.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -378,6 +378,7 @@ select make_date(2013, 7, 15);
378378
select make_date(-44, 3, 15);
379379
select make_time(8, 20, 0.0);
380380
-- should fail
381+
select make_date(0, 7, 15);
381382
select make_date(2013, 2, 30);
382383
select make_date(2013, 13, 1);
383384
select make_date(2013, 11, -1);

‎src/test/regress/sql/timestamp.sql

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -240,4 +240,7 @@ SELECT '' AS to_char_12, to_char(d, 'FF1 FF2 FF3 FF4 FF5 FF6 ff1 ff2 ff3 ff4 ff
240240
) d(d);
241241

242242
-- timestamp numeric fields constructor
243-
SELECT make_timestamp(2014,12,28,6,30,45.887);
243+
SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887);
244+
SELECT make_timestamp(-44, 3, 15, 12, 30, 15);
245+
-- should fail
246+
select make_timestamp(0, 7, 15, 12, 30, 15);

0 commit comments

Comments
 (0)